2 changed the answer to use datetime2(3)
source | link

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)

enter image description here

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 datesrandom dates from 01-jan-201901-jan-2019 and today (23-july-201923-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):

enter image description hereenter image description here

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):

enter image description here

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)

enter image description here

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)

enter image description here

1
source | link

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):

enter image description here