Omitting the Milliseconds in a Date

Question

Hello,

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.

Thanks.

Answer 1

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

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.

Answer 3

try this

 declare @DATE datetime
 select @DATE = '1/1/2009 1:23:11.923'



 SELECT convert(datetime,CONVERT(char(35),@DATE,120))

or with date functions only

DECLARE @DATE DATETIME
SELECT @DATE = '1/1/2009 1:23:11.923'

SELECT DATEADD(SECOND, DATEDIFF(SECOND, 39000, @DATE), 39000)

Answer 4

DATEADD(SECOND, DATEDIFF(SECOND, 0, < your datetime column >), 0)

May need to change the 0 to something else to prevent an overflow error. Don't have a SQL Server at hand right now to verify.

While this method does not appear to be intuitive at first sight, have a look here for the rationale behind it: http://www.karaszi.com/SQLServer/info_datetime.asp#GettingRidOfTimePortion

EDIT:see comments

Answer 5

If you don't want to use string conversions, here's a solution:

DECLARE @TheDate datetime, @Today datetime
SET @TheDate = GetDate()

SET @Today = DateAdd(dd, DateDiff(dd, 0, @TheDate), 0)
SELECT DateAdd(s, DateDiff(s, @Today, @TheDate), @Today)

Answer 6

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

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

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.