SET NOCOUNT ON
USE tempdb
GO

DECLARE @t_0a       time(0)
DECLARE @t_0b       time(0)
DECLARE @t_1a       time(1)
DECLARE @t_1b       time(1)
DECLARE @t_2a       time(2)
DECLARE @t_2b       time(2)
DECLARE @t_3a       time(3)
DECLARE @t_3b       time(3)
DECLARE @t_4a       time(4)
DECLARE @t_4b       time(4)
DECLARE @t_5a       time(5)
DECLARE @t_5b       time(5)
DECLARE @t_6a       time(6)
DECLARE @t_6b       time(6)
DECLARE @t_7a       time(7)
DECLARE @t_7b       time(7)

DECLARE @dta        datetime
DECLARE @dtb        datetime

DECLARE @sdta       smalldatetime
DECLARE @sdtb       smalldatetime

DECLARE @dt2_0a     datetime2(0)
DECLARE @dt2_0b     datetime2(0)
DECLARE @dt2_1a     datetime2(1)
DECLARE @dt2_1b     datetime2(1)
DECLARE @dt2_2a     datetime2(2)
DECLARE @dt2_2b     datetime2(2)
DECLARE @dt2_3a     datetime2(3)
DECLARE @dt2_3b     datetime2(3)
DECLARE @dt2_4a     datetime2(4)
DECLARE @dt2_4b     datetime2(4)
DECLARE @dt2_5a     datetime2(5)
DECLARE @dt2_5b     datetime2(5)
DECLARE @dt2_6a     datetime2(6)
DECLARE @dt2_6b     datetime2(6)
DECLARE @dt2_7a     datetime2(7)
DECLARE @dt2_7b     datetime2(7)

SELECT @t_0a   = GETDATE(), @t_0b   = SYSDATETIME()
SELECT @t_1a   = GETDATE(), @t_1b   = SYSDATETIME()
SELECT @t_2a   = GETDATE(), @t_2b   = SYSDATETIME()
SELECT @t_3a   = GETDATE(), @t_3b   = SYSDATETIME()
SELECT @t_4a   = GETDATE(), @t_4b   = SYSDATETIME()
SELECT @t_5a   = GETDATE(), @t_5b   = SYSDATETIME()
SELECT @t_6a   = GETDATE(), @t_6b   = SYSDATETIME()
SELECT @t_7a   = GETDATE(), @t_7b   = SYSDATETIME()

SELECT @dta    = GETDATE(), @dtb    = SYSDATETIME()
SELECT @sdta   = GETDATE(), @sdtb   = SYSDATETIME()

SELECT @dt2_0a = GETDATE(), @dt2_0b = SYSDATETIME()
SELECT @dt2_1a = GETDATE(), @dt2_1b = SYSDATETIME()
SELECT @dt2_2a = GETDATE(), @dt2_2b = SYSDATETIME()
SELECT @dt2_3a = GETDATE(), @dt2_3b = SYSDATETIME()
SELECT @dt2_4a = GETDATE(), @dt2_4b = SYSDATETIME()
SELECT @dt2_5a = GETDATE(), @dt2_5b = SYSDATETIME()
SELECT @dt2_6a = GETDATE(), @dt2_6b = SYSDATETIME()
SELECT @dt2_7a = GETDATE(), @dt2_7b = SYSDATETIME()

PRINT '================ DataType: time(n)'
SELECT 'time(0)'       AS 'DataType     ', @t_0a   AS 'GETDATE()                  ', @t_0b   AS 'SYSDATETIME()              ', CONVERT(varchar(27), @t_0a,   121) AS 'Converted (121, GET)', CONVERT(varchar(27), @t_0b,   121) AS 'Converted (121, SYS)', DATALENGTH(@t_0b) AS 'DataLength'
SELECT 'time(1)'       AS 'DataType     ', @t_1a   AS 'GETDATE()                  ', @t_1b   AS 'SYSDATETIME()              ', CONVERT(varchar(27), @t_1a,   121) AS 'Converted (121, GET)', CONVERT(varchar(27), @t_1b,   121) AS 'Converted (121, SYS)', DATALENGTH(@t_1b) AS 'DataLength'
SELECT 'time(2)'       AS 'DataType     ', @t_2a   AS 'GETDATE()                  ', @t_2b   AS 'SYSDATETIME()              ', CONVERT(varchar(27), @t_2a,   121) AS 'Converted (121, GET)', CONVERT(varchar(27), @t_2b,   121) AS 'Converted (121, SYS)', DATALENGTH(@t_2b) AS 'DataLength'
SELECT 'time(3)'       AS 'DataType     ', @t_3a   AS 'GETDATE()                  ', @t_3b   AS 'SYSDATETIME()              ', CONVERT(varchar(27), @t_3a,   121) AS 'Converted (121, GET)', CONVERT(varchar(27), @t_3b,   121) AS 'Converted (121, SYS)', DATALENGTH(@t_3b) AS 'DataLength'
SELECT 'time(4)'       AS 'DataType     ', @t_4a   AS 'GETDATE()                  ', @t_4b   AS 'SYSDATETIME()              ', CONVERT(varchar(27), @t_4a,   121) AS 'Converted (121, GET)', CONVERT(varchar(27), @t_4b,   121) AS 'Converted (121, SYS)', DATALENGTH(@t_4b) AS 'DataLength'
SELECT 'time(5)'       AS 'DataType     ', @t_5a   AS 'GETDATE()                  ', @t_5b   AS 'SYSDATETIME()              ', CONVERT(varchar(27), @t_5a,   121) AS 'Converted (121, GET)', CONVERT(varchar(27), @t_5b,   121) AS 'Converted (121, SYS)', DATALENGTH(@t_5b) AS 'DataLength'
SELECT 'time(6)'       AS 'DataType     ', @t_6a   AS 'GETDATE()                  ', @t_6b   AS 'SYSDATETIME()              ', CONVERT(varchar(27), @t_6a,   121) AS 'Converted (121, GET)', CONVERT(varchar(27), @t_6b,   121) AS 'Converted (121, SYS)', DATALENGTH(@t_6b) AS 'DataLength'
SELECT 'time(7)'       AS 'DataType     ', @t_7a   AS 'GETDATE()                  ', @t_7b   AS 'SYSDATETIME()              ', CONVERT(varchar(27), @t_7a,   121) AS 'Converted (121, GET)', CONVERT(varchar(27), @t_7b,   121) AS 'Converted (121, SYS)', DATALENGTH(@t_7b) AS 'DataLength'

PRINT '================ DataType: Classic datetime and smalldatetime'
SELECT 'datetime'      AS 'DataType     ', @dta    AS 'GETDATE()                  ', @dtb    AS 'SYSDATETIME()              ', CONVERT(varchar(27), @dta,    121) AS 'Converted (121, GET)', CONVERT(varchar(27), @dtb,    121) AS 'Converted (121, SYS)', DATALENGTH(@dtb)  AS 'DataLength'

SELECT 'smalldatetime' AS 'DataType     ', @sdta   AS 'GETDATE()                  ', @sdtb   AS 'SYSDATETIME()              ', CONVERT(varchar(27), @sdta,   121) AS 'Converted (121, GET)', CONVERT(varchar(27), @sdtb,   121) AS 'Converted (121, SYS)', DATALENGTH(@sdtb) AS 'DataLength'

PRINT '================ DataType: datetime2(n)'
SELECT 'datetime2(0)'  AS 'DataType     ', @dt2_0a AS 'GETDATE()                  ', @dt2_0b AS 'SYSDATETIME()              ', CONVERT(varchar(27), @dt2_0a, 121) AS 'Converted (121, GET)', CONVERT(varchar(27), @dt2_0b, 121) AS 'Converted (121, SYS)', DATALENGTH(@dt2_0b) AS 'DataLength'
SELECT 'datetime2(1)'  AS 'DataType     ', @dt2_1a AS 'GETDATE()                  ', @dt2_1b AS 'SYSDATETIME()              ', CONVERT(varchar(27), @dt2_1a, 121) AS 'Converted (121, GET)', CONVERT(varchar(27), @dt2_1b, 121) AS 'Converted (121, SYS)', DATALENGTH(@dt2_1b) AS 'DataLength'
SELECT 'datetime2(2)'  AS 'DataType     ', @dt2_2a AS 'GETDATE()                  ', @dt2_2b AS 'SYSDATETIME()              ', CONVERT(varchar(27), @dt2_2a, 121) AS 'Converted (121, GET)', CONVERT(varchar(27), @dt2_2b, 121) AS 'Converted (121, SYS)', DATALENGTH(@dt2_2b) AS 'DataLength'
SELECT 'datetime2(3)'  AS 'DataType     ', @dt2_3a AS 'GETDATE()                  ', @dt2_3b AS 'SYSDATETIME()              ', CONVERT(varchar(27), @dt2_3a, 121) AS 'Converted (121, GET)', CONVERT(varchar(27), @dt2_3b, 121) AS 'Converted (121, SYS)', DATALENGTH(@dt2_3b) AS 'DataLength'
SELECT 'datetime2(4)'  AS 'DataType     ', @dt2_4a AS 'GETDATE()                  ', @dt2_4b AS 'SYSDATETIME()              ', CONVERT(varchar(27), @dt2_4a, 121) AS 'Converted (121, GET)', CONVERT(varchar(27), @dt2_4b, 121) AS 'Converted (121, SYS)', DATALENGTH(@dt2_4b) AS 'DataLength'
SELECT 'datetime2(5)'  AS 'DataType     ', @dt2_5a AS 'GETDATE()                  ', @dt2_5b AS 'SYSDATETIME()              ', CONVERT(varchar(27), @dt2_5a, 121) AS 'Converted (121, GET)', CONVERT(varchar(27), @dt2_5b, 121) AS 'Converted (121, SYS)', DATALENGTH(@dt2_5b) AS 'DataLength'
SELECT 'datetime2(6)'  AS 'DataType     ', @dt2_6a AS 'GETDATE()                  ', @dt2_6b AS 'SYSDATETIME()              ', CONVERT(varchar(27), @dt2_6a, 121) AS 'Converted (121, GET)', CONVERT(varchar(27), @dt2_6b, 121) AS 'Converted (121, SYS)', DATALENGTH(@dt2_6b) AS 'DataLength'
SELECT 'datetime2(7)'  AS 'DataType     ', @dt2_7a AS 'GETDATE()                  ', @dt2_7b AS 'SYSDATETIME()              ', CONVERT(varchar(27), @dt2_7a, 121) AS 'Converted (121, GET)', CONVERT(varchar(27), @dt2_7b, 121) AS 'Converted (121, SYS)', DATALENGTH(@dt2_7b) AS 'DataLength'
GO

-- ============================================================================================================================================

-- Default output truncates
DECLARE @dt  datetime
DECLARE @dt2 datetime2(7)

SET @dt  = SYSDATETIME()
SET @dt2 = SYSDATETIME()

SELECT
    @dt                              AS 'Default',
    CONVERT(varchar(27), @dt,  121)  AS 'Converted (121, SYS)'

SELECT
    @dt2                             AS 'Default',
    @dt2                             AS 'Default with a wide label..',
    CONVERT(varchar(27), @dt2, 121)  AS 'Converted (121, SYS)'
GO

-- Default output test with table
DECLARE @table TABLE (
    Col1    int,
    Col2    datetime2
)

INSERT INTO @table (Col1, Col2) VALUES (1, SYSDATETIME())
INSERT INTO @table (Col1, Col2) VALUES (1, SYSUTCDATETIME())

SELECT
    Col2                             AS 'Default',
    Col2                             AS 'Default with a wide label..',
    CONVERT(varchar(27), Col2, 121)  AS 'Converted (121, SYS)'
FROM @table
GO

-- ==== Default precision is 7
DECLARE @t   time
DECLARE @dt2 datetime2

SET @t   = SYSDATETIME()
SET @dt2 = SYSDATETIME()

SELECT DATEPART(ns, @t), DataLength(@t)
SELECT DATEPART(ns, @dt2), DataLength(@dt2)
GO

-- ==== Conversion
DECLARE @t   time
DECLARE @dt2 datetime2
DECLARE @sdt smalldatetime
DECLARE @s   char(27)

SET @dt2 = SYSDATETIME()
SET @sdt = SYSDATETIME()

SET @t = @dt2
SELECT @dt2 AS 'Source (datetime2(7))      ', @t AS 'Implicit conversion to time(7) from datetime2(7)'

SET @t = @sdt
SELECT @sdt AS 'Source (smalldatetime)     ', @t AS 'Implicit conversion to time(7) from smalldatetime'

SET @s = '2008-12-17 15:32:42.5625123'
SET @dt2 = @s
SELECT @s AS 'Source String', @dt2 AS 'Variable (datetime2(7)) from string'

-- Converting a time variable to a date-and-time format works, showing only the time portion
SET @t = SYSDATETIME()
SELECT @t AS 'Source time', CONVERT(varchar, @t, 121) AS 'Converted time(7)'
GO

-- ==== Adding nanoseconds
DECLARE @dt2a datetime2
DECLARE @dt2b datetime2

SET @dt2b = SYSDATETIME()

-- Adding 49 nanoseconds does not change the resulting value
SET @dt2a = DATEADD(ns, 49, @dt2b)
SELECT
    @dt2b AS 'Source                     ',
    DATEPART(ns, @dt2b) AS 'NanoSeconds',
    @dt2a AS 'Result: DateAdd(ns, 49)    ',
    DATEPART(ns, @dt2a) AS 'NanoSeconds'

-- Adding 50 nanoseconds does change the resulting value
SET @dt2a = DATEADD(ns, 50, @dt2b)
SELECT
    @dt2b AS 'Source                     ',
    DATEPART(ns, @dt2b) AS 'NanoSeconds',
    @dt2a AS 'Result: DateAdd(ns, 50)    ',
    DATEPART(ns, @dt2a) AS 'NanoSeconds'
GO

-- ==== Use strings to create over-precise values (time)
-- Base
DECLARE @s1 varchar(19)
DECLARE @s2 varchar(19)
DECLARE @s3 varchar(19)
DECLARE @s4 varchar(19)
DECLARE @t time(7)

SET @s1 = '11:59:59.9999999'    -- Initial full-precision time(7)
SET @s2 = '11:59:59.99999999'
SET @s3 = '11:59:59.999999999'
--SET @s3 = '11:59:59.999999949'
--SET @s3 = '11:59:59.999999950'
SET @s4 = '11:59:59.9999999999'

SET @t = @s1
SELECT
    LEN(@s1)         AS 'String Len',
    @s1              AS 'Source',
    @t               AS 'Result          ',
    DATEPART(ns, @t) AS 'NanoSeconds'

SET @t = @s2
SELECT
    LEN(@s2)         AS 'String Len',
    @s2              AS 'Source',
    @t               AS 'Result          ',
    DATEPART(ns, @t) AS 'NanoSeconds'

SET @t = @s3
SELECT
    LEN(@s3)         AS 'String Len',
    @s3              AS 'Source',
    @t               AS 'Result          ',
    DATEPART(ns, @t) AS 'NanoSeconds'

SET @t = @s4
SELECT
    LEN(@s4)         AS 'String Len',
    @s4              AS 'Source',
    @t               AS 'Result          ',
    DATEPART(ns, @t) AS 'NanoSeconds'


DECLARE @ct     int
DECLARE @str1   varchar(30)
DECLARE @t_7    time(7)

-- Initial full-precision datetime2
--SET @str1 = '12:00:00.9999999'
SET @str1 = '12:00:00.0000000'
--SET @str1 = '15:32:42.5625123'

SET @ct = 0
WHILE @ct < 10 BEGIN
    SET @ct += 1
    SET @t_7 = @str1

    SELECT
        @ct AS 'Ct',
        LEN(@str1) AS 'String Length  ',
        @str1 AS 'Source',
        @t_7 AS 'Result                          ',
        DATEPART(ns, @t_7) AS 'NanoSeconds'

    SET @str1 += '9'
END
GO