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).

Hugo Kornelis (9/1/2010)

---

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.

Robert Dennyson (9/2/2010)

---

It should work

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