Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


datename with varchar data type


datename with varchar data type

Author
Message
mm-1009269
mm-1009269
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 182
What am I missing??


, cast(stuff(stuff(stuff(cast(CRM_Orders.CREATED_AT as varchar(17)), 9, 0, ' '), 12, 0, ':'), 15, 0, ':') as datetime) AS [Service Order Created At] /* this works fine */
, DATENAME (MONTH,cast(stuff(stuff(stuff(cast(CRM_Orders.CREATED_AT as varchar(17)), 9, 0, ' '), 12, 0, ':'), 15, 0, ':') as datetime),CRM_Orders.CRM_Orders.CREATED_AT) AS [Service Order Created At Month] /*this one doesn't work */



It says: 'The datename function requires 2 argument(s).'

But I thought I did have two arguments, 'MONTH" and 'CRM_Orders.CRM_Orders.CREATED_AT'

I'm not new to datename so I'm sure I'm missing something stupid....

Thanks!
Michelle
Bruce W Cassidy
Bruce W Cassidy
Right there with Babe
Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)

Group: General Forum Members
Points: 787 Visits: 1033
DATENAME() does require two arguments. The first argument tells SQL Server which part of the date name you want (year, month, day, etc). The second argument is the date for which you want the name.
mm-1009269
mm-1009269
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 182
I thought I had that though.... with the 'DATENAME (MONTH...'

Sad

Michelle
Bruce W Cassidy
Bruce W Cassidy
Right there with Babe
Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)

Group: General Forum Members
Points: 787 Visits: 1033
Okay, it looks to me as though you are trying to do the following:

1. display the start of the day on which the service order was created
2. display the start of the month on which the service order was created

Can I suggest you try the following code?


declare @x table(
CREATED_AT datetime not null default getdate()
);
insert into @x values(default);

select
1
, dateadd(day, datediff(day, 0, CRM_Orders.CREATED_AT), 0) as [Service Order Created At]
, dateadd(month, datediff(month, 0, CRM_Orders.CREATED_AT), 0) as [Service Order Created At Month]
from @x CRM_Orders



The way you were trying to do it was a bit overcomplicated, and also prone to errors because it relies on the default date format for the server. If the server changes default date format, your code would break.

Bruce W Cassidy
Bruce W Cassidy
Right there with Babe
Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)

Group: General Forum Members
Points: 787 Visits: 1033
Okay, I thought about the code you posted some more, and I suspect that your originating date is actually stored as an integer in the form:

YYYYMMDDHHMMSS

That explains why you would need this bit of code:

cast(stuff(stuff(stuff(cast(CREATED_AT as varchar(17)), 9, 0, ' '), 12, 0, ':'), 15, 0, ':') as datetime)



That takes the integer, converts it to a string, adds in the necessary formatting characters, and converts the result to a datetime.

In which case, you might want to try something like the following:


declare @x table(
CREATED_AT bigint not null
);
insert into @x values(20090717102705);

select
1
, CRM_Orders.CREATED_AT as [Service Order Created At]
, dateadd(month, datediff(month, 0, CRM_Orders.CREATED_AT), 0) as [Service Order Created At Month]
from (
select cast(stuff(stuff(stuff(cast(CREATED_AT as varchar(17)), 9, 0, ' '), 12, 0, ':'), 15, 0, ':') as datetime) as CREATED_AT
from @x
) CRM_Orders;



What I have done is created a sub-select that does the date conversion once. Thereafter I can just treat it as a date (so I don't have to do the conversion every time I want to use it.)

Also, my apologies. If this is what you are doing, then the date conversions you were doing are fine. That's SQL Server's standard date format, and your code would work correctly regardless of the default date format.

mm-1009269
mm-1009269
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 182
Oh, that very interesting Bruce! I never thought about doing that way....

My .CREATED_AT is actually stored as a numeric(15,0).

I'm going to try what you suggested and see how that works out. But it might be tomorrow before a reply back because I have to go home soon.

Thanks!! I've learned a lot.

Michelle
Bruce W Cassidy
Bruce W Cassidy
Right there with Babe
Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)Right there with Babe (787 reputation)

Group: General Forum Members
Points: 787 Visits: 1033
mmunson (7/16/2009)
My .CREATED_AT is actually stored as a numeric(15,0).

Ah well, I was close. Smile

mmunson (7/16/2009)
Thanks!! I've learned a lot.

You're welcome!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search