SQL Server 2008 and milliseconds

sql-server-2008 datetime milliseconds
Mass Dot Net picture Mass Dot Net · Nov 16, 2011 · Viewed 11.5k times · Source

In SQL Server 2008, why do the following queries return the same value?

-- These all return 2011-01-01 23:59:59.997
SELECT CAST('2011-01-01 23:59:59.997' as datetime)
SELECT CAST('2011-01-01 23:59:59.998' as datetime)

And why does the following query round to the next day?

-- Returns 2011-01-02 00:00:00.000
SELECT CAST('2011-01-01 23:59:59.999' as datetime)

Answer

Andrew picture Andrew · Nov 16, 2011

The accuracy of DateTime within SQL Server has always been to 1/300s of a second (3.33ms), so any value that does not divide precisely gets rounded.

  • 997 stays as it is
  • 998 will round to 997
  • 999 will round up to 000

To get additional accuracy, there is the DateTime2 data type, available in SQL Server 2008 onwards, that can be accurate to 7 decimal places.

Related questions

SQL datetime update time to 00:00:00 from all other time values

Is there a way to update all columns in table from 2010-12-31 23:59:59.000 to 2010-12-31 00:00:00.000 Something like this?? UPDATE t1 SET [Posting Date] = [Posting Date] with ms = 00:00:00.000 WHERE ms = other than 00:00:00.000 GO

Read More
Sql query to insert datetime in SQL Server

I want to insert a datetime value into a table(SQL Server) using the sql query below insert into table1(approvaldate)values(18-06-12 10:34:09 AM); But I get this Error msg. Incorrect syntax near '10'. I tried it with …

Read More
How can I select the first day of a month in SQL?

I just need to select the first day of the month of a given datetime variable. I know it's quite easy to do using this kind of code: select CAST(CAST(YEAR(@mydate) AS VARCHAR(4)) + '/' + CAST(MONTH(@mydate) …

Read More