2 changed the answer to use datetime2(3) edited Jul 24 at 11:56 Marcello Miorelli 11.4k2929 gold badges8484 silver badges188188 bronze badges I have done it using `varcharDATETIME2(233)` only. IAs 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 dates`random dates` from 01-jan-2019`01-jan-2019` and today (23-july-2019`23-july-2019`) ``````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 VARCHARDATETIME2(233) 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 VARCHARDATETIME2(233) SELECT @DT = CONVERT(VARCHARDATETIME2(193), DATEADD(SECOND, ABS(CHECKSUM(NEWID()) % 17625600), '2019-01-01'),120) +  '.000' --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=REPLACE(THE_DATE,SUBSTRINGTHE_NEW_DATE= DATEADD(THE_DATE,21,3)MILLISECOND, CASE WHEN (ROW_NUMBER() OVER (ORDER BY THE_DATE) ) < 10 THEN '00' ELSE CASE WHEN (ROW_NUMBER() OVER (ORDER BY, THE_DATE) ) < 100 THEN '0' ELSE '' END END + CAST( ROW_NUMBER() OVER (ORDER BY THE_DATE) AS VARCHAR(3))) FROM #T1 `````` and the resultthis is what I get: (partial view): I have done it using `varchar(23)` only. I create a temp table for testing purposes, and populate it with 999 different dates from 01-jan-2019 and today (23-july-2019) ``````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 VARCHAR(23) 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 VARCHAR(23) SELECT @DT = CONVERT(VARCHAR(19), DATEADD(SECOND, ABS(CHECKSUM(NEWID()) % 17625600), '2019-01-01'),120) + '.000' 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=REPLACE(THE_DATE,SUBSTRING(THE_DATE,21,3), CASE WHEN (ROW_NUMBER() OVER (ORDER BY THE_DATE) ) < 10 THEN '00' ELSE CASE WHEN (ROW_NUMBER() OVER (ORDER BY THE_DATE) ) < 100 THEN '0' ELSE '' END END + CAST( ROW_NUMBER() OVER (ORDER BY THE_DATE) AS VARCHAR(3))) FROM #T1 `````` and the result is (partial view): 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`) ``````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) 1 answered Jul 23 at 18:06 Marcello Miorelli 11.4k2929 gold badges8484 silver badges188188 bronze badges I have done it using `varchar(23)` only. I create a temp table for testing purposes, and populate it with 999 different 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 VARCHAR(23) 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 VARCHAR(23) SELECT @DT = CONVERT(VARCHAR(19), DATEADD(SECOND, ABS(CHECKSUM(NEWID()) % 17625600), '2019-01-01'),120) + '.000' 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=REPLACE(THE_DATE,SUBSTRING(THE_DATE,21,3), CASE WHEN (ROW_NUMBER() OVER (ORDER BY THE_DATE) ) < 10 THEN '00' ELSE CASE WHEN (ROW_NUMBER() OVER (ORDER BY THE_DATE) ) < 100 THEN '0' ELSE '' END END + CAST( ROW_NUMBER() OVER (ORDER BY THE_DATE) AS VARCHAR(3))) FROM #T1 `````` and the result is (partial view):