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