Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

datename with varchar data type Expand / Collapse
Author
Message
Posted Thursday, July 16, 2009 4:02 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 6, 2009 4:10 PM
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
Post #754531
Posted Thursday, July 16, 2009 4:08 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:12 PM
Points: 758, Visits: 1,031
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.
Post #754533
Posted Thursday, July 16, 2009 4:11 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 6, 2009 4:10 PM
Points: 85, Visits: 182
I thought I had that though.... with the 'DATENAME (MONTH...'



Michelle
Post #754535
Posted Thursday, July 16, 2009 4:15 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:12 PM
Points: 758, Visits: 1,031
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.
Post #754537
Posted Thursday, July 16, 2009 4:34 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:12 PM
Points: 758, Visits: 1,031
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.
Post #754542
Posted Thursday, July 16, 2009 4:41 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 6, 2009 4:10 PM
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
Post #754547
Posted Thursday, July 16, 2009 4:52 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:12 PM
Points: 758, Visits: 1,031
mmunson (7/16/2009)
My .CREATED_AT is actually stored as a numeric(15,0).

Ah well, I was close. :)

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

You're welcome!
Post #754550
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse