-3

I encounter situation like this where I try to add Millisecond into my DateTime.

enter image description here

In some cases, it didn't give me the exact number and it got rounded to something else. Is it normal ?

          CONVERT(varchar(125), dateadd(millisecond,ms,datetime), 21) as ZDateTimeMs
Ken White
  • 123,280
  • 14
  • 225
  • 444
user3015739
  • 635
  • 2
  • 8
  • 20
  • 2
    Are you using the `datetime2` data type in SQL Server? The legacy `datetime` data type has a 1/300th second (3.333 millisecond) resolution for... [Sybase and UNIX reasons](https://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc32300.1600/doc/html/san1390612192030.html). – AlwaysLearning Feb 08 '23 at 04:09
  • 2
    Please don't use images for data, use formatted text. – Dale K Feb 08 '23 at 04:15

1 Answers1

1

From datetime:

datetime values are rounded to increments of .000, .003, or .007 seconds, as shown in the following table.

User-specified value System stored value

01/01/98 23:59:59.999   1998-01-02 00:00:00.000
01/01/98 23:59:59.995

01/01/98 23:59:59.996

01/01/98 23:59:59.997

01/01/98 23:59:59.998   1998-01-01 23:59:59.997
01/01/98 23:59:59.992

01/01/98 23:59:59.993

01/01/98 23:59:59.994   1998-01-01 23:59:59.993
01/01/98 23:59:59.990

01/01/98 23:59:59.991   1998-01-01 23:59:59.990
Dale K
  • 25,246
  • 15
  • 42
  • 71
HABO
  • 15,314
  • 5
  • 39
  • 57
  • So, I should be creating a different data type ? – user3015739 Feb 08 '23 at 05:20
  • It seems declaring it as a STRING like what I have done probably resolve the automatic rounding behavior. – user3015739 Feb 08 '23 at 05:37
  • 3
    @user3015739 using a string for datetimes will cause you pain for the lifetime of the system. As the comment below your question says, the correct solution is to use `datetime2`. – Dale K Feb 08 '23 at 06:38