July 16, 2009 at 4:02 pm
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
July 16, 2009 at 4:08 pm
[font="Verdana"]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.[/font]
July 16, 2009 at 4:11 pm
I thought I had that though.... with the 'DATENAME (MONTH...'
🙁
Michelle
July 16, 2009 at 4:15 pm
[font="Verdana"]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.
[/font]
July 16, 2009 at 4:34 pm
[font="Verdana"]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.
[/font]
July 16, 2009 at 4:41 pm
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
July 16, 2009 at 4:52 pm
mmunson (7/16/2009)
My .CREATED_AT is actually stored as a numeric(15,0).
[font="Verdana"]Ah well, I was close. :)[/font]
mmunson (7/16/2009)
Thanks!! I've learned a lot.
[font="Verdana"]You're welcome![/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply