September 1, 2010 at 8:24 am
Dave62 (9/1/2010)
SELECT DATEADD(day, DATEDIFF(day, '20100101', CURRENT_TIMESTAMP), '20100101');The only thing I don't like about this method is the hard-coded string. I suppose a dynamic expression could replace it but it would start to become a little tedious.
I don't really see the hard-coded string as a problem. The only requirement is that you use the same date twice, it doesn't matter what date you choose. There is no risk of overflow, as there are way more integers in the int domain, then days in the datetime domain.
September 1, 2010 at 8:26 am
webrunner (9/1/2010)
DECLARE @a INT
DECLARE @b-2 DATETIME
The above code executed successfully for me in SQL 2005 but gave the error listed in the answer when I ran it in SQL 2000**. So I think it is dependent on the SQL version, at least in the form expressed above.
Which version of SQL Server 2005? When I run it, I get "Msg 257, Level 16, State 3, Line 3
Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query."
(I am using Microsoft SQL Server 2005 - 9.00.4053.00)
September 1, 2010 at 8:34 am
Hugo Kornelis (9/1/2010)
...I don't really see the hard-coded string as a problem. The only requirement is that you use the same date twice, it doesn't matter what date you choose. There is no risk of overflow, as there are way more integers in the int domain, then days in the datetime domain.
You're right Hugo. I guess we have 2 ways to get the same results without the integer addition.
But one way is a little more concise with a little less hard-coding. 😉
September 1, 2010 at 9:07 am
Dave62 (9/1/2010)
SELECT DATEADD(day, DATEDIFF(day, '20100101', CURRENT_TIMESTAMP), '20100101');The only thing I don't like about this method is the hard-coded string. I suppose a dynamic expression could replace it but it would start to become a little tedious.
I don't mind having 2 converts in there because mbova407's original example had 2. This method below will return the same result and datatype as mbova407's without the need for the integer addition.
Select Convert(Datetime, CONVERT(varChar(10), getDate(), 101));
The latter method is considerably more expensive though. The SQL Musings from the Desert article by Lynn Pettis has been dissected by quite few posts and from what I remember, the verdict is pretty clear: dateadd and datediff combination is far cheaper than conversion functions. 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).
<!-- Begin blatant self promotion
One of my answers on the ask side has in-depth explanation of datetime internals, and according to Matt Whitfield, it "sounds spot on". Here is the link: http://ask.sqlservercentral.com/questions/16420/php-with-mssql-strtotime-with-mssql-datetime-column
End blatant self promotion -->
Oleg
September 1, 2010 at 9:26 am
Hugo Kornelis (9/1/2010)
webrunner (9/1/2010)
DECLARE @a INT
DECLARE @b-2 DATETIME
The above code executed successfully for me in SQL 2005 but gave the error listed in the answer when I ran it in SQL 2000**. So I think it is dependent on the SQL version, at least in the form expressed above.
Which version of SQL Server 2005? When I run it, I get "Msg 257, Level 16, State 3, Line 3
Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query."
(I am using Microsoft SQL Server 2005 - 9.00.4053.00)
Sorry, my mistake. (BTW, My SQL 2005 is version 9.0.3080.)
This code runs OK in both versions:
DECLARE @a INT
DECLARE @b-2 DATETIME
This code throws an error in both versions:
DECLARE @a INT
DECLARE @b-2 DATETIME
I mixed up the "SET @a = @b-2" version with the "SET @b-2= @a" version in my earlier post. I seem to have run one version in SQL 2000 and the other in SQL 2005.
Attention to detail, anyone? :hehe:
Thanks,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
September 1, 2010 at 9:32 am
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.:-)
September 1, 2010 at 12:58 pm
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.
September 2, 2010 at 12:02 am
Thanks for the question!
September 2, 2010 at 2:43 am
It should work
SELECT COVERT(VARCHAR,GETDATE(),111);
September 2, 2010 at 4:01 am
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.
September 7, 2010 at 10:50 am
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
March 19, 2012 at 10:09 am
Good and straight forward question. Thanks
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply