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

When you convert a time value to datetime, extra information is added to the value. This is because the datetime data type contains both date and time information. The time data type, on the other hand, only contains time information. Therefore, date information is added to the value when you perform such a conversion. Specifically, the date is set to ‘1900-01-01’.

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 datetime.

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

Result:

+------------------+-------------------------+
| time             | datetime                |
|------------------+-------------------------|
| 23:15:59.0040070 | 1900-01-01 23:15:59.003 |
+------------------+-------------------------+

When you convert from time to datetime, the date component is set to 1900-01-01.

Also notice that the time value itself is presented differently across both of these data types. The time data type adds a zero to the end (because it has a higher precision – its default scale is 7). On the other hand, datetime values use a lower scale and are rounded to increments of .000, .003, or .007 seconds. If you find this problematic, consider converting to datetime2 instead.

Example 2 – Lower Precision/Scale

In the previous example, the time value had a higher fractional seconds precision than the datetime value. This is because it uses a default scale of 7. But we can change this to a lower value if required.

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. We can specify the scale by appending a number in brackets to the data type.

Here’s what happens if I explicitly set the time value to have a lower scale than the datetime value.

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

Result:

+----------+-------------------------+
| time     | datetime                |
|----------+-------------------------|
| 23:15:59 | 1900-01-01 23:15:59.000 |
+----------+-------------------------+

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(datetime, @thetime) AS 'datetime';

Result:

+------------------+-------------------------+
| time             | datetime                |
|------------------+-------------------------|
| 23:15:59.0040070 | 1900-01-01 23:15:59.003 |
+------------------+-------------------------+

Example 4 – Implicit Conversion

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

DECLARE @thetime time, @thedatetime datetime
SET @thetime = '23:15:59.004007'
SET @thedatetime = @thetime
SELECT 
  @thetime AS 'time',
  @thedatetime AS 'datetime';

Result:

+------------------+-------------------------+
| time             | datetime                |
|------------------+-------------------------|
| 23:15:59.0040070 | 1900-01-01 23:15:59.003 |
+------------------+-------------------------+

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 datetime 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, @thedatetime datetime
SET @thetime = '23:15:59.004007'
SET @thedatetime = @thetime
SET @thedatetime = DATEADD(year, 120, @thedatetime)
SELECT 
  @thetime AS 'time',
  @thedatetime AS 'datetime';

Result:

+------------------+-------------------------+
| time             | datetime                |
|------------------+-------------------------|
| 23:15:59.0040070 | 2020-01-01 23:15:59.003 |
+------------------+-------------------------+

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