SQL_VARIANT and DATETIME2

Question

Lets say you have the following table:

CREATE TABLE [dbo].[Test]
(
    [Value] [sql_variant] NOT NULL
)

And lets insert some DATETIME2 values:

INSERT INTO [dbo].[Test]
(
    [Value]
)
VALUES
(
    SYSUTCDATETIME()
)

Now, lets see the results:

SELECT
    [Value],
    SQL_VARIANT_PROPERTY([Value], 'BaseType') AS BaseType
FROM
    [dbo].[Test]


Value                     BaseType
-----------------------------------
2019-11-25 19:39:43.274   datetime2
2019-11-25 19:43:49.735   datetime2

Why do the values get truncated?

Answer 1

When working with sql_variant, you need to be specific about the datatype you wish to return; otherwise SQL Server will infer the display format. The data, however, has not been truncated. If you try the following, for example, you'll see you get a datetime2(7) as the result for Value_DT2:

SELECT [Value],
       CONVERT(datetime2,[Value]) AS Value_DT2,
       SQL_VARIANT_PROPERTY([Value], 'BaseType') AS BaseType
FROM [dbo].[Test];