2

Why does the millisecond portion of a MS SQL Server DateTime data type always end in 0, 3 or 7? I've hit F5 on a SELECT GetDate() numerous times, and the milliseconds consistently end in one of those three numbers.

To demonstrate, I've made this example, with the results below:

DECLARE @t TABLE (dt DateTime)
DECLARE @i INT = 1
WHILE @i <= 2000
    BEGIN
        INSERT INTO @t(dt) VALUES(GetDate())
        SET @i = @i + 1
    END
SELECT DISTINCT dt FROM @t

2016-08-05 15:38:06.380
2016-08-05 15:38:06.383
2016-08-05 15:38:06.420
2016-08-05 15:38:06.630
2016-08-05 15:38:06.817
2016-08-05 15:38:07.053
2016-08-05 15:38:07.057
2016-08-05 15:38:07.230
2016-08-05 15:38:07.233
2016-08-05 15:38:07.453
2016-08-05 15:38:07.457
2016-08-05 15:38:07.510
2016-08-05 15:38:07.570
2016-08-05 15:38:07.623
2016-08-05 15:38:07.690
2016-08-05 15:38:07.693
2016-08-05 15:38:07.747
2016-08-05 15:38:07.750
2016-08-05 15:38:07.800
2016-08-05 15:38:07.803
2016-08-05 15:38:07.857
2016-08-05 15:38:07.910
2016-08-05 15:38:07.913
2016-08-05 15:38:07.967
2016-08-05 15:38:07.970
2016-08-05 15:38:08.027
2016-08-05 15:38:08.080
2016-08-05 15:38:08.130
InbetweenWeekends
  • 1,405
  • 3
  • 23
  • 28
  • Is the thousands place of this number even considered to be significant? – Tim Biegeleisen Aug 05 '16 at 19:57
  • Interesting observation, I am wondering that now as well – lucas Aug 05 '16 at 20:06
  • 1
    **tl;dr**: use `datetime2`. @lucas no need to wonder; it was already answered before you commented. – underscore_d Aug 05 '16 at 20:07
  • This answer seems to be a duplicate of [question 21825615](https://stackoverflow.com/questions/21825615/why-sql-server-datetime-type-saves-time-in-ticks-of-1-300-of-a-sec). TL; DR: SQL Server is based on Sybase, and Sybase's datetimes were made the way they were due to Unix – Kris Lawton May 12 '22 at 13:33

0 Answers0