How to add 1 milliseconds to a datetime string?

Question

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.

Answer 1

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

Answer 2

The DateAdd function is what you are looking for.

Use millisecond as the first parameter to the function, to tell it that you are adding milliseconds. Then use 1 as the second parameter, for the number of milliseconds to add.

Here is an example, grabbing the current time into a variable, and then adding one millisecond to it and saving the result as a second variable, and then printing each variable

Declare @RightNow as DateTime2
Declare @RightNowPlusAMillisecond as DateTime2

Select @RightNow = Getdate()
Select @RightNowPlusAMillisecond = DateAdd(millisecond,1,@RightNow)

Print @RightNow
Print @RightNowPlusAMillisecond

Results:

2019-07-23 08:25:38.3500000
2019-07-23 08:25:38.3510000

Note:

As Forrest points out in another answer, the datetime data type does not guarantee millisecond precision. It rounds to increments of .000, .003, or .007 seconds. If you want millisecond precision, use datetime2.

Answer 3

@Doug-Deden has the right starting point, but I just wanted to try to answer what I thought was the original intention of the question - how to apply it to a result set with increasing milliseconds per row.

In that case, you can use ROW_NUMBER and a Common Table Expression (edit as needed for you table structure, including joins, etc.).

Select to show values:

;WITH CTE AS (
SELECT t.my_id, t.my_date_column, ROW_NUMBER() OVER (ORDER BY my_date_column, my_id DESC) AS R
FROM Table1 t
)
SELECT TOP 1000 *, DATEADD(MILLISECOND, R, CAST(my_date_column AS datetime2)) [new_date]
FROM CTE
ORDER BY my_date_column

Update joins back to original table:

;WITH CTE AS (
SELECT t.my_id, t.my_date_column, ROW_NUMBER() OVER (ORDER BY my_date_column, my_id DESC) AS R
FROM Table1 t
)
UPDATE t SET 
my_date_column = DATEADD(MILLISECOND, R, CAST(my_date_column AS datetime2))
FROM CTE c
     JOIN Table1 t ON c.my_id = t.my_id

Answer 4

I have done it using DATETIME2(3).

As you can see on the query below, it is more economic:

declare @dt1 datetime2(3)
declare @dt2 datetime2

SELECT @DT1 = SYSDATETIME()
SELECT @DT2=  SYSDATETIME()

SELECT [THE LENGTH OF DATETIME2]=DATALENGTH(@DT2)
      ,[THE LENGTH OF DATETIME2(3)]=DATALENGTH(@DT1)

The differences between datetime and datetime2 are well explained here.

For this exercise I create a temp table for testing purposes, and populate it with 999 different random dates from 01-jan-2019 and today (23-july-2019)

and then in order, I set the milliseconds from 1 to 999

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOEXEC OFF

IF OBJECT_ID ('TEMPDB..#T1') IS NOT NULL
   DROP TABLE #T1

CREATE TABLE #t1(the_date DATETIME2(3) NOT NULL PRIMARY KEY CLUSTERED )
GO

-- run this 999 times - hopefully there will be no duplicates
-- SELECT 204*24*60*60 - today is 23-july-2019 - the 203rd day of the year
    DECLARE @DT DATETIME2(3)
    SELECT @DT = CONVERT(DATETIME2(3),
           DATEADD(SECOND, ABS(CHECKSUM(NEWID()) % 17625600), 
                   '2019-01-01'),120) 

    --SELECT @DT

    IF NOT EXISTS( SELECT 1 FROM #T1 WHERE THE_DATE = @DT) 
    INSERT INTO #T1 VALUES (@DT)
GO 999


--check it out what we have
SELECT * FROM #T1

--get the date and the new date
SELECT 
 THE_DATE
,THE_NEW_DATE= DATEADD(MILLISECOND, ROW_NUMBER() OVER (ORDER BY THE_DATE), THE_DATE ) 
 FROM #T1

and this is what I get: (partial view)

Answer 5

One of the other posters is correct; DATETIME (in T-SQL) is not accurate to the millisecond (it is accurate to the centisecond).

For that level of accuracy, you want to use DATETIME2.

Here is an example of converting a string datetime to datetime2, then adding 1 millisecond, and lastly, converting back to a string.

select convert(
            varchar(MAX), --in T-SQL, varchar length is optional
            dateadd(
                millisecond,
                1,
                convert(
                    datetime2,
                    '2019-07-23 12:01:23.11'
                )
            )
        )