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 «««123

What will happen? Expand / Collapse
Author
Message
Posted Wednesday, September 1, 2010 9:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:08 PM
Points: 6,002, Visits: 8,265
Oleg Netchaev (9/1/2010)
Hugo's script looks somewhat unusual because it uses some varchar value which is guaranteed to be dateformat independent valid datetime value (YYYYMMDD is always translated correctly regardless of local format). What is usually used instead is 0, because it is simply zero date, shorter to type and much faster to convert (rather than rely on engine's ability to convert varchar to datetime), i.e.

select dateadd(day, datediff(day, 0, current_timestamp), 0);

Why faster is because how datetime is stored internally (4 bytes for number of days from zero date and 4 bytes for number of ticks from midnight of today).

Correct. Thanks, Oleg!

The reason I use a date in the 'yyyymmdd' format instead of an integer value is because it's easier for the eye (assuming an eye that is somewhat used to this format) to interpret as a date. As a matter of principle, I always use 'yyyymmdd' for date constants in my code. I would never dream of using the integer constant 40421 to represent Sep 1st, 2010 (and I hope noone would even consider that), even though it does indeed convert to datetime faster than the string constant '20100901'. And I don't like to make an exception for representing the date Jan 1st, 1900.
But I do agree that it's quite common to use 0 in this particular construction, and that it does not really hurt.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #978917
Posted Wednesday, September 1, 2010 12:58 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, April 25, 2014 8:31 AM
Points: 513, Visits: 430
Hugo Kornelis (9/1/2010)
Oleg Netchaev (9/1/2010)
...
select dateadd(day, datediff(day, 0, current_timestamp), 0);

Why faster is because how datetime is stored internally (4 bytes for number of days from zero date and 4 bytes for number of ticks from midnight of today).


The reason I use a date in the 'yyyymmdd' format instead of an integer value is because it's easier for the eye (assuming an eye that is somewhat used to this format) to interpret as a date. As a matter of principle, I always use 'yyyymmdd' for date constants in my code. I would never dream of using the integer constant 40421 to represent Sep 1st, 2010 (and I hope noone would even consider that), even though it does indeed convert to datetime faster than the string constant '20100901'. And I don't like to make an exception for representing the date Jan 1st, 1900.
But I do agree that it's quite common to use 0 in this particular construction, and that it does not really hurt.

Thank you Oleg & Hugo. I was not surprised by the question (not to imply anything about the question itself) as I have faced these conversion issues earlier but was looking at the discussion anyways and found this interesting piece of information.
Post #979073
Posted Thursday, September 2, 2010 12:02 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 2:00 PM
Points: 2,163, Visits: 2,191
Thanks for the question!
Post #979308
Posted Thursday, September 2, 2010 2:43 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, January 6, 2012 5:27 AM
Points: 13, Visits: 108
It should work

SELECT COVERT(VARCHAR,GETDATE(),111);

Post #979353
Posted Thursday, September 2, 2010 4:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:08 PM
Points: 6,002, Visits: 8,265
Robert Dennyson (9/2/2010)
It should work

SELECT COVERT(VARCHAR,GETDATE(),111);


If you add the missing letter N (CONVERT), it does work. It is an explicit conversion from datetime to varchar format, using the yyyy/mm/dd format (a format that is not locale-neutral and hence not recommended in various places, but if you need the yyyy/mm/dd format for a report, this is the function to use).

However, I fail to see the relation with the question discussed here, which is about implicit conversion from datetime to int, not about explicit conversion from datetime to varchar.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #979381
Posted Tuesday, September 7, 2010 10:50 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 8:47 AM
Points: 8,832, Visits: 9,389
good question. some good discussion too. but shifting from explicit conversion of datetime to int (fast and efficient) to explicit conversion of datetime to a very local varchar date format seemed a little bizarre.

Tom
Post #981716
Posted Monday, March 19, 2012 10:09 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 10:52 PM
Points: 483, Visits: 244
Good and straight forward question. Thanks
Post #1269066
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse