Getting date or time only from a Datetime value

Wed Nov 12, 2008 by Peter Larsson in optimization, sql-server-2008, algorithms, sql-server-2005, sql-server-2000

SELECT GETDATE() AS theFullDateTime,
       DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AS theDateOnly,
       DATEADD(DAY, DATEDIFF(DAY, GETDATE(), 0), GETDATE()) AS theTimeOnly

SELECT GETDATE() AS theFullDateTime,
       DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()), '19000101') AS theDateOnly,
       DATEADD(DAY, DATEDIFF(DAY, GETDATE(), '19000101'), GETDATE()) AS theTimeOnly

Legacy Comments


Bill Curnow 2008-11-12	re: Getting date or time only from a Datetime value SELECT GETDATE() AS fullDateTime, CONVERT(VARCHAR, GETDATE(), 101) AS dateOnly, CONVERT(VARCHAR, GETDATE(), 108) AS timeOnly

Peso 2008-11-12	re: Getting date or time only from a Datetime value The basic idea is to keep the values as datetimes, even if only time is preserved. And you should use style 114 instead if 108.

Bill Curnow 2008-11-12	re: Getting date or time only from a Datetime value "Should" is largely subjective, but yes, 114 preserves the entire time right down to milliseconds.