This article contains examples of converting a time value to a datetime2 value in SQL Server.

When you convert a time value to datetime2, extra information is added to the value. This is because the datetime2 data type contains both date and time information. The time data type, on the other hand, only contains time information.

More specifically, the date is set to ‘1900-01-01’ (unless it happens to get rounded up to ‘1900-01-02’), the time component is copied, and according to the Microsoft documentation, the time zone offset is set to 00:00 (even though the datetime2 data type is not time zone aware and doesn’t preserve any time zone offset).

When the fractional seconds precision of the datetime2(n) value is greater than the time(n) value, the value is rounded up.

Example 1 – Explicit Conversion using CAST()

Here’s an example of an explicit conversion. In this case, I use the CAST() function directly within the SELECT statement to explicitly convert from time to datetime2.

DECLARE @thetime time;
SET @thetime = '23:15:59.004007';
SELECT 
  @thetime AS 'time',
  CAST(@thetime AS datetime2) AS 'datetime2';

Result:

+------------------+-----------------------------+
| time             | datetime2                   |
|------------------+-----------------------------|
| 23:15:59.0040070 | 1900-01-01 23:15:59.0040070 |
+------------------+-----------------------------+

When you convert from time to datetime2, a date component is added and set to 1900-01-01.

However, there are scenarios where the date could be rounded up to 1900-01-02. This will depend on the fractional seconds and what precision you use. See below for an example of this.

Example 2 – Fractional Seconds Precision

You can get different results depending on the fractional seconds precision that’s assigned to each data type. This will depend on the actual value of the fractional part.

In the previous example, both data types used the same fractional seconds precision. This is because I didn’t specify a scale (to define their precision) and therefore they both used their default scale values (both happen to be 7).

Just to be clear, scale is the number of digits to the right of the decimal point in a number. Precision is the total number of digits in the number.

Anyway, in that example I only assigned 6 decimal places to the initial value, therefore, a zero is added to the end.

Here’s what happens if I specify a higher precision for the time value compared to the datetime2 value:

DECLARE @thetime time(7);
SET @thetime = '23:15:59.1234567';
SELECT 
  @thetime AS 'time',
  CAST(@thetime AS datetime2(4)) AS 'datetime2(4)';

Result:

+------------------+--------------------------+
| time             | datetime2(4)             |
|------------------+--------------------------|
| 23:15:59.1234567 | 1900-01-01 23:15:59.1235 |
+------------------+--------------------------+

By specifying a scale of 4 to the datetime2 value, the result is reduced to 4 decimal places, and in this case it’s rounded up.

As you might expect, it’s not just the fractional part that can get rounded up. Here’s an example of where the fractional part causes the minutes and seconds to be rounded up:

DECLARE @thetime time(7);
SET @thetime = '23:15:59.7654321';
SELECT 
  @thetime AS 'time',
  CAST(@thetime AS datetime2(0)) AS 'datetime2(0)';

Result:

+------------------+---------------------+
| time             | datetime2(0)        |
|------------------+---------------------|
| 23:15:59.7654321 | 1900-01-01 23:16:00 |
+------------------+---------------------+

However, you can get different results for the same data by changing the precision. If we increase the precision, even just by one decimal place, we get this:

DECLARE @thetime time(7);
SET @thetime = '23:15:59.7654321';
SELECT 
  @thetime AS 'time',
  CAST(@thetime AS datetime2(1)) AS 'datetime2(1)';

Result:

+------------------+-----------------------+
| time             | datetime2(1)          |
|------------------+-----------------------|
| 23:15:59.7654321 | 1900-01-01 23:15:59.8 |
+------------------+-----------------------+

So in this case the minutes and seconds weren’t rounded up (but milliseconds were).

Here’s what happens if I set the time value to use a lower precision scale than the datetime2 value.

DECLARE @thetime time(0);
SET @thetime = '23:15:59.004007';
SELECT 
  @thetime AS 'time',
  CAST(@thetime AS datetime2) AS 'datetime2';

Result:

+----------+-----------------------------+
| time     | datetime2                   |
|----------+-----------------------------|
| 23:15:59 | 1900-01-01 23:15:59.0000000 |
+----------+-----------------------------+

And while we’re at it, here’s an example of where our precision scale can result in the fractional seconds causing the date to be rounded to the next day:

DECLARE @thetime time(7);
SET @thetime = '23:59:59.9999999';
SELECT 
  @thetime AS 'time',
  CAST(@thetime AS datetime2(0)) AS 'datetime2(0)';

Result:

+------------------+---------------------+
| time             | datetime2(0)        |
|------------------+---------------------|
| 23:59:59.9999999 | 1900-01-02 00:00:00 |
+------------------+---------------------+

Example 3 – Explicit Conversion using CONVERT()

This is the same as the first example, except this time I use the CONVERT() function instead of CAST().

DECLARE @thetime time;
SET @thetime = '23:15:59.004007';
SELECT 
  @thetime AS 'time',
  CONVERT(datetime2, @thetime) AS 'datetime2';

Result:

+------------------+-----------------------------+
| time             | datetime2                   |
|------------------+-----------------------------|
| 23:15:59.0040070 | 1900-01-01 23:15:59.0040070 |
+------------------+-----------------------------+

Example 4 – Implicit Conversion

Here’s an example of doing the same thing, but using an implicit type conversion.

DECLARE @thetime time, @thedatetime2 datetime2;
SET @thetime = '23:15:59.004007';
SET @thedatetime2 = @thetime;
SELECT 
  @thetime AS 'time',
  @thedatetime2 AS 'datetime2';

Result:

+------------------+-----------------------------+
| time             | datetime2                   |
|------------------+-----------------------------|
| 23:15:59.0040070 | 1900-01-01 23:15:59.0040070 |
+------------------+-----------------------------+

So we get the same result, regardless of whether it’s an explicit or implicit conversion.

This is an implicit conversion because we’re not using a conversion function to explicitly convert it. We’re simply assigning the value from a variable of one data type to a variable of another data type. In this case, SQL Server performs an implicit conversion behind the scenes when we try to assign the time value to a datetime2 variable.

Example 5 – Change the Date

If you need to change the date (but keep the same time), you can use the DATEADD() function.

DECLARE @thetime time, @thedatetime2 datetime2;
SET @thetime = '23:15:59.004007';
SET @thedatetime2 = @thetime;
SET @thedatetime2 = DATEADD(year, 120, @thedatetime2);
SELECT 
  @thetime AS 'time',
  @thedatetime2 AS 'datetime2';

Result:

+------------------+-----------------------------+
| time             | datetime2                   |
|------------------+-----------------------------|
| 23:15:59.0040070 | 2020-01-01 23:15:59.0040070 |
+------------------+-----------------------------+

In this case I add 120 to the year value, which brings it to 2020.