This article contains examples of converting a datetime value to a datetimeoffset value in SQL Server.
When you convert a datetime value to datetimeoffset, the resulting value will depend on the fractional seconds precision that you assign to datetimeoffset, as well as any time zone offset you specify.
The datetime data type has a maximum of 3 digits for its fractional seconds part. Its accuracy is rounded to increments of .000, .003, or .007 seconds.
The datetimeoffset data type, on the other hand, allows you to specify a fractional seconds precision from 0 to 7. If you don’t specify this, it will use 7 (the default). It also has a time zone offset and can preserve any offsets in the original value. However, datetime has no time zone awareness, so there are no existing values to preserve. In this case, the time zone offset defaults to +00:00.
SQL Server actually has the TODATETIMEOFFSET() function, which is specifically designed to convert a date/time value to datetimeoffset and add a time zone offset. However, there’s a subtle detail to be aware of when using this function, and I explain this below (with examples).
Example 1 – Implicit Conversion
First, here’s an example of an implicit conversion between datetime and datetimeoffset.
DECLARE @thedatetime datetime, @thedatetimeoffset datetimeoffset(7); SET @thedatetime = '2025-05-21 10:15:30.123'; SET @thedatetimeoffset = @thedatetime; SELECT @thedatetime AS 'datetime', @thedatetimeoffset AS 'datetimeoffset(7)';
Result:
+-------------------------+------------------------------------+ | datetime | datetimeoffset(7) | |-------------------------+------------------------------------| | 2025-05-21 10:15:30.123 | 2025-05-21 10:15:30.1233333 +00:00 | +-------------------------+------------------------------------+
This is an implicit conversion because we’re not using a conversion function (like the ones below) to explicitly convert it. In this case, SQL Server performs an implicit conversion behind the scenes when we try to assign the datetime value to a datetimeoffset variable.
We can see that the datetimeoffset variable has more fractional seconds precision, and we end up with a fractional part of 1233333 (vs 123 for the datetime value). We also end up with a time zone offset of +00:00.
Using 7 fractional seconds precision causes datetimeoffset to use 10 bytes for storage (11 bytes if you include the byte that stores its precision). By way of comparison, datetime uses only 8 bytes. However, you can reduce the precision of the datetimeoffset value by replacing the 7 with a lower number. This is the same concept as when using the datetime2 data type. See Convert ‘datetime’ to ‘datetime2’ in SQL Server for examples of how this can affect the end result.
Example 2 – Rounding
The datetime data type is rounded to increments of .000, .003, or .007 seconds. Even if you explicitly set it to another value, it will be rounded. This could cause you a lot of confusion if you’re not aware of how it works. Not only can it cause confusion when using datetime by itself, it can cause extra confusion when converting that value to another data type.
Here’s an example that demonstrates what I mean.
DECLARE @thedatetime datetime, @thedatetimeoffset datetimeoffset; SET @thedatetime = '2025-05-21 10:15:30.125'; SET @thedatetimeoffset = @thedatetime; SELECT @thedatetime AS 'datetime', @thedatetimeoffset AS 'datetimeoffset(7)';
Result:
+-------------------------+------------------------------------+ | datetime | datetimeoffset(7) | |-------------------------+------------------------------------| | 2025-05-21 10:15:30.127 | 2025-05-21 10:15:30.1266667 +00:00 | +-------------------------+------------------------------------+
In this example, I set the fractional seconds to 125 but datetime rounded it up to 127 (because this data type can only be rounded to increments of .000, .003, or .007 seconds).
The datetimeoffset value on the other hand, set the fractional seconds to 1266667.
However, if we’d simply set the initial value to datetimeoffset in the first place, its fractional part would’ve returned 1250000.
Example 3 – 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 between datetime and datetimeoffset.
DECLARE @thedatetime datetime; SET @thedatetime = '2025-05-21 10:15:30.125'; SELECT @thedatetime AS 'datetime', CAST(@thedatetime AS datetimeoffset(7)) AS 'datetimeoffset(7)';
Result:
+-------------------------+------------------------------------+ | datetime | datetimeoffset(7) | |-------------------------+------------------------------------| | 2025-05-21 10:15:30.127 | 2025-05-21 10:15:30.1266667 +00:00 | +-------------------------+------------------------------------+
Example 4 – Explicit Conversion using CONVERT()
Here’s an example of an explicit conversion using the CONVERT() function instead of CAST().
DECLARE @thedatetime datetime; SET @thedatetime = '2025-05-21 10:15:30.125'; SELECT @thedatetime AS 'datetime', CONVERT(datetimeoffset(7), @thedatetime) AS 'datetimeoffset(7)';
Result:
+-------------------------+------------------------------------+ | datetime | datetimeoffset(7) | |-------------------------+------------------------------------| | 2025-05-21 10:15:30.127 | 2025-05-21 10:15:30.1266667 +00:00 | +-------------------------+------------------------------------+
Example 5 – Changing the Time Zone Offset
If you’re going into all the trouble of converting your datetime values to datetimeoffset, you probably need the timezone offset. And there’s a strong possibility that you want it set to something other than +00:00.
Fortunately, you can use the TODATETIMEOFFSET() function to change the offset.
You can also use this function to convert the original datetime value to a datetimeoffset value. This function accepts a date/time value (that can resolve to a datetime2 value), and an offset value.
Here’s an example:
DECLARE @thedatetime datetime, @thedatetimeoffset datetimeoffset; SET @thedatetime = '2025-05-21 10:15:30.125'; SET @thedatetimeoffset = TODATETIMEOFFSET(@thedatetime, '+07:00'); SELECT @thedatetime AS 'datetime', @thedatetimeoffset AS 'datetimeoffset';
Result:
+-------------------------+------------------------------------+ | datetime | datetimeoffset(7) | |-------------------------+------------------------------------| | 2025-05-21 10:15:30.127 | 2025-05-21 10:15:30.1270000 +07:00 | +-------------------------+------------------------------------+
And here’s an example using the function within the SELECT statement:
DECLARE @thedatetime datetime = '2025-05-21 10:15:30.125'; SELECT @thedatetime AS 'datetime', TODATETIMEOFFSET(@thedatetime, '+07:00') AS 'datetimeoffset';
Result:
+-------------------------+------------------------------------+ | datetime | datetimeoffset(7) | |-------------------------+------------------------------------| | 2025-05-21 10:15:30.127 | 2025-05-21 10:15:30.1270000 +07:00 | +-------------------------+------------------------------------+
One important point about the TODATETIMEOFFSET() function is that it uses the same fractional precision as the date/time argument passed to it. In this case it’s a datetime argument, so it has a scale of 3 (i.e. 3 fractional seconds). This may or may not be an issue for you. If it is, you can always convert it to a datetimeoffset first, then pass that converted value to TODATETIMEOFFSET().
Example:
DECLARE @thedatetime datetime, @thedatetimeoffset datetimeoffset(7); SET @thedatetime = '2025-05-21 10:15:30.125'; SET @thedatetimeoffset = @thedatetime; SELECT @thedatetime AS 'datetime', @thedatetimeoffset AS 'datetimeoffset', TODATETIMEOFFSET(@thedatetimeoffset, '+07:00') AS 'Modified';
Result (using vertical output):
datetime | 2025-05-21 10:15:30.127 datetimeoffset | 2025-05-21 10:15:30.1266667 +00:00 Modified | 2025-05-21 10:15:30.1266667 +07:00