Omitting the Milliseconds in a Date

Question

When I select from SQL Server, I want to get a date, but omit the millisecond value, and I want it to be as a date type. So if I have a value 1/1/2009 1:23:11.923, I want to omit the millisecond but retain the date type, so that it will be the value 1/1/2009 1:23:11.000 (I know you really can't omit the millisecond value with a date, just want it to be zero).

Is there a function in SQL Server to do this? Or do I have to write my own function? Again, I don't want it as a varchar type, but a datetime type.

Answer 1

Use DATETIME2, a new datatype in SQL Server 2008 that supports fractional precision:

SELECT   CONVERT(DATETIME2(0),SYSDATETIME()) [yyyy-mm-dd hh:mm:ss] , CONVERT(DATETIME2(1),SYSDATETIME()) [yyyy-mm-dd hh:mm:ss.f] , CONVERT(DATETIME2(2),SYSDATETIME()) [yyyy-mm-dd hh:mm:ss.ff] , CONVERT(DATETIME2(3),SYSDATETIME()) [yyyy-mm-dd hh:mm:ss.fff] , CONVERT(DATETIME2(4),SYSDATETIME()) [yyyy-mm-dd hh:mm:ss.ffff] , CONVERT(DATETIME2(5),SYSDATETIME()) [yyyy-mm-dd hh:mm:ss.fffff] , CONVERT(DATETIME2(6),SYSDATETIME()) [yyyy-mm-dd hh:mm:ss.ffffff] , CONVERT(DATETIME2(7),SYSDATETIME()) [yyyy-mm-dd hh:mm:ss.fffffff] 

The conversion will round to the nearest unit, eg:

2014-09-04 09:35:47.0162993 as DATETIME2(4) ->  2014-09-04 09:35:47.0163 

Alternatively, on SQL 2005 and eariler:

SELECT   original  = GETDATE() , [floor]   = DATEADD(ms,-DATEPART(ms,GETDATE()),GETDATE()) , [ceiling] = DATEADD(ms,1000-DATEPART(ms,GETDATE()),GETDATE()) , [rounded] = DATEADD(ms,CASE WHEN DATEPART(ms,GETDATE()) < 500 THEN 0 ELSE 1000 END-DATEPART(ms,GETDATE()),GETDATE()) 

This is a bit faster than converting to and from a string representation.

Answer 2

Use:

SELECT CONVERT(DATETIME, CONVERT(VARCHAR(19), GETDATE(), 120)) 

This:

CONVERT(VARCHAR(19), GETDATE(), 120) 

...omits the milliseconds, returning a VARCHAR. So you CAST/CONVERT that into a DATETIME in order to work with the desired data type.

See this link for a list of various date/time formats you can work with.