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