2RkB6XuQ

1select top 10 ModifiedOn 
2    from [SCHEMA].[dbo].[TABLE]
3  where FIELD between '2019-07-23 00:00' and '2019-07-23 23:59' 
4     
5;WITH CTE AS (
6SELECT t.my_id, t.my_date_column, ROW_NUMBER() OVER (ORDER BY my_date_column, my_id DESC) AS R
7FROM Table1 t
8)
9SELECT TOP 1000 *, DATEADD(MILLISECOND, R, CAST(my_date_column AS datetime2)) [new_date]
10FROM CTE
11ORDER BY my_date_column 
12     
13;WITH CTE AS (
14SELECT t.my_id, t.my_date_column, ROW_NUMBER() OVER (ORDER BY my_date_column, my_id DESC) AS R
15FROM Table1 t
16)
17UPDATE t SET 
18my_date_column = DATEADD(MILLISECOND, R, CAST(my_date_column AS datetime2))
19FROM CTE c
20     JOIN Table1 t ON c.my_id = t.my_id 
21     
22Declare @RightNow as DateTime2
23Declare @RightNowPlusAMillisecond as DateTime2
24
25Select @RightNow = Getdate()
26Select @RightNowPlusAMillisecond = DateAdd(millisecond,1,@RightNow)
27
28Print @RightNow
29Print @RightNowPlusAMillisecond 
30     
312019-07-23 08:25:38.3500000
322019-07-23 08:25:38.3510000 
33     
34declare @dt1 datetime2(3)
35declare @dt2 datetime2
36
37SELECT @DT1 = SYSDATETIME()
38SELECT @DT2=  SYSDATETIME()
39
40SELECT [THE LENGTH OF DATETIME2]=DATALENGTH(@DT2)
41      ,[THE LENGTH OF DATETIME2(3)]=DATALENGTH(@DT1) 
42     
43SET NOCOUNT ON
44SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
45SET NOEXEC OFF
46
47IF OBJECT_ID ('TEMPDB..#T1') IS NOT NULL
48   DROP TABLE #T1
49
50CREATE TABLE #t1(the_date DATETIME2(3) NOT NULL PRIMARY KEY CLUSTERED )
51GO
52
53-- run this 999 times - hopefully there will be no duplicates
54-- SELECT 204*24*60*60 - today is 23-july-2019 - the 203rd day of the year
55    DECLARE @DT DATETIME2(3)
56    SELECT @DT = CONVERT(DATETIME2(3),
57           DATEADD(SECOND, ABS(CHECKSUM(NEWID()) % 17625600), 
58                   '2019-01-01'),120) 
59
60    --SELECT @DT
61
62    IF NOT EXISTS( SELECT 1 FROM #T1 WHERE THE_DATE = @DT) 
63    INSERT INTO #T1 VALUES (@DT)
64GO 999
65
66
67--check it out what we have
68SELECT * FROM #T1
69
70--get the date and the new date
71SELECT 
72 THE_DATE
73,THE_NEW_DATE= DATEADD(MILLISECOND, ROW_NUMBER() OVER (ORDER BY THE_DATE), THE_DATE ) 
74 FROM #T1 
75     
76select convert(
77            varchar(MAX), --in T-SQL, varchar length is optional
78            dateadd(
79                millisecond,
80                1,
81                convert(
82                    datetime2,
83                    '2019-07-23 12:01:23.11'
84                )
85            )
86        )