snippetsqlMajor
How to add 1 milliseconds to a datetime string?
Viewed 0 times
addmillisecondshowstringdatetime
Problem
Based on a select, I can return x rows like this:
We have all milliseconds with 0.
Is there a way to add 1 by 1 milliseconds, so the select would look like this:
I'm trying to create a cursor or even a update with no success.
This is the query to get the results I want:
There are 81k values. The field is
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.