SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What will happen?


What will happen?

Author
Message
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18515 Visits: 12426
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
rjv_rnjn
rjv_rnjn
SSC Eights!
SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)

Group: General Forum Members
Points: 939 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.
UMG Developer
UMG Developer
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3958 Visits: 2204
Thanks for the question!
Robert Dennyson
Robert Dennyson
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 108
It should work

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


Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18515 Visits: 12426
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
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25563 Visits: 12494
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

zymos
zymos
Right there with Babe
Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)Right there with Babe (746 reputation)

Group: General Forum Members
Points: 746 Visits: 263
Good and straight forward question. Thanks
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