HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMajor

How to add 1 milliseconds to a datetime string?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
addmillisecondshowstringdatetime

Problem

Based on a select, I can return x rows like this:

1 2019-07-23 10:14:04.000
1 2019-07-23 10:14:11.000
2 2019-07-23 10:45:32.000
1 2019-07-23 10:45:33.000


We have all milliseconds with 0.

Is there a way to add 1 by 1 milliseconds, so the select would look like this:

1 2019-07-23 10:14:04.001
1 2019-07-23 10:14:11.002
2 2019-07-23 10:45:32.003
1 2019-07-23 10:45:33.004


I'm trying to create a cursor or even a update with no success.

This is the query to get the results I want:

select top 10 ModifiedOn 
    from [SCHEMA].[dbo].[TABLE]
  where FIELD between '2019-07-23 00:00' and '2019-07-23 23:59'


There are 81k values. The field is DATETIME.

Solution

Datetime is not precise to the level of 1 millisecond. What you are asking for is not possible unless you change to a different datatype (i.e. datetime2).

Documentation

Important quote:


Accuracy Rounded to increments of .000, .003, or .007 seconds

Context

StackExchange Database Administrators Q#243534, answer score: 34

Revisions (0)

No revisions yet.