This article contains examples of converting a time value to a datetimeoffset value in SQL Server using Transact-SQL.

When you convert a time value to datetimeoffset, the date is set to ‘1900-01-01’ and the time is copied. A time zone offset is added and set to +00:00.

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

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

Result:

+------------------+------------------------------------+
| time             | datetimeoffset                     |
|------------------+------------------------------------|
| 23:15:59.1234567 | 1900-01-01 23:15:59.1234567 +00:00 |
+------------------+------------------------------------+

So a date part is added and set to ‘1900-01-01’, the time is copied, and a time zone offset is added and set to +00:00.

Example 2 – Precision

In the previous example, both data types use their default precision/scale (7). This is because I didn’t add a scale in brackets (the scale determines the fractional seconds precision). By using a scale of 7, we can see that both data types are able to represent a time value that is precise to 7 decimal places.

In other words, when I initially set the @thetime variable, I included 7 decimal places in the value (specifically, 1234567). Both the ‘time’ and the ‘datetimeoffset’ data types were successfully able to represent these because they both used a scale of 7. Again, we know they used 7 because that’s the default value.

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 reduce the fractional seconds precision if required.

Here are some examples that demonstrate what happens when the data types are set to use different fractional seconds precision:

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

Result:

+------------------+------------------------------------+
| time             | datetimeoffset                     |
|------------------+------------------------------------|
| 23:15:59.1234567 | 1900-01-01 23:15:59.0000000 +00:00 |
+------------------+------------------------------------+

In this case I explicitly set the @thetime variable to use a scale of 7. But when I cast this to datetimeoffset, I set the scale to 0. Therefore, the result for the datetimeoffset value is less fractional seconds precision. On my system, 7 decimal places are still displayed, but they are all 0.

Here it is again, but this time I increase the fractional seconds precision to 3 for the datetimeoffset value:

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

Result:

+------------------+------------------------------------+
| time             | datetimeoffset                     |
|------------------+------------------------------------|
| 23:15:59.1234567 | 1900-01-01 23:15:59.1230000 +00:00 |
+------------------+------------------------------------+

So it uses the first 3 fractional seconds (milliseconds).

However, if we increase the fractional seconds precision to 4, look what happens:

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

Result:

+------------------+------------------------------------+
| time             | datetimeoffset                     |
|------------------+------------------------------------|
| 23:15:59.1234567 | 1900-01-01 23:15:59.1235000 +00:00 |
+------------------+------------------------------------+

In the next example I increase the fractional part’s value so that it causes the non-fractional part of the datetimeoffset value to be rounded up:

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

Result:

+------------------+------------------------------------+
| time             | datetimeoffset                     |
|------------------+------------------------------------|
| 23:15:59.7654321 | 1900-01-01 23:16:00.0000000 +00:00 |
+------------------+------------------------------------+

In this case, the minutes were rounded up and the seconds set to zero.

Let’s swap it around so that the datetimeoffset has a higher precision than the time value:

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

Result:

+------------------+------------------------------------+
| time             | datetimeoffset                     |
|------------------+------------------------------------|
| 23:15:59.1235000 | 1900-01-01 23:15:59.1235000 +00:00 |
+------------------+------------------------------------+

I declared the @thetime variable to use a scale of 4, but then used a scale of 7 when converting it to the datetimeoffset data type. Using a precision of 7 is unnecessary, as it can’t use a higher precision than what was already assigned.

Also, any lower precision rounding has already occurred by the time that it’s converted to the (higher precision) datetimeoffset data type. Notice that the time data type rounded the fractional seconds up from the initial value that I assigned to it. This rounding effect also flowed through to the datetimeoffset value.

Example 3 – Explicit Conversion using CONVERT()

Here’s an example using the CONVERT() function instead of CAST().

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

Result:

+------------------+------------------------------------+
| time             | datetimeoffset                     |
|------------------+------------------------------------|
| 23:15:59.1234567 | 1900-01-01 23:15:59.1234567 +00:00 |
+------------------+------------------------------------+

Example 4 – Implicit Conversion

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

DECLARE @thetime time, @thedatetimeoffset datetimeoffset;
SET @thetime = '23:15:59.1234567';
SET @thedatetimeoffset = @thetime;
SELECT 
  @thetime AS 'time',
  @thedatetimeoffset AS 'datetimeoffset';

Result:

+------------------+------------------------------------+
| time             | datetimeoffset                     |
|------------------+------------------------------------|
| 23:15:59.1234567 | 1900-01-01 23:15:59.1234567 +00:00 |
+------------------+------------------------------------+

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 datetimeoffset 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, @thedatetimeoffset datetimeoffset;
SET @thetime = '23:15:59.1234567';
SET @thedatetimeoffset = @thetime;
SET @thedatetimeoffset = DATEADD(year, 285, @thedatetimeoffset);
SELECT 
  @thetime AS 'time',
  @thedatetimeoffset AS 'datetimeoffset';

Result:

+------------------+------------------------------------+
| time             | datetimeoffset                     |
|------------------+------------------------------------|
| 23:15:59.1234567 | 2185-01-01 23:15:59.1234567 +00:00 |
+------------------+------------------------------------+

In this case I add 285 to the year value, which brings it to 2185.