This article looks at the main differences between the datetime2 and datetimeoffset data types in SQL Server.
Both data types are used for storing date and time values. Both are very similar, but with one key difference; the datetimeoffset stores the time zone offset.
This also results in datetimeoffset using more storage space than datetime2, so you would only use datetimeoffset if you need the time zone offset.
Here’s a table that outlines the key differences between these two types.
| Feature | datetimeoffset | datetime2 |
|---|---|---|
| SQL Compliant (ANSI & ISO 8601) | Yes | Yes |
| Date Range | 0001-01-01 through 9999-12-31 | 0001-01-01 through 9999-12-31 |
| Time Range | 00:00:00 through 23:59:59.9999999 | 00:00:00 through 23:59:59.9999999 |
| Character Length |
26 positions minimum 34 maximum |
19 positions minimum 27 maximum |
| Storage Size | 8 to 10 bytes, depending on the precision*
* Plus 1 byte to store the precision |
6 to 8 bytes, depending on the precision*
* Plus 1 byte to store the precision |
| Accuracy | 100 nanoseconds | 100 nanoseconds |
| Fractional second precision | Yes | Yes |
| User-defined fractional second precision | Yes | Yes |
| Time zone offset range | -14:00 through +14:00 | None |
| Time zone offset aware and preservation | Yes | No |
| Daylight saving aware | No | No |
Should I use ‘datetime2’ or ‘datetimeoffset’?
This depends on whether or not you need to include a time zone offset.
If you need to include a time zone offset, then you’ll need to use datetimeoffset.
If not, then use datetime2, as you’ll save storage space and eliminate any potential issues with having a (potentially wrong) time zone offset in your data.
Example 1 – Basic Comparison
Here’s a quick example to demonstrate the basic difference between datetime2 and datetimeoffset.
DECLARE @thedatetimeoffset datetimeoffset(7), @thedatetime2 datetime2(7); SET @thedatetimeoffset = '2025-05-21 10:15:30.5555555 +07:30'; SET @thedatetime2 = @thedatetimeoffset; SELECT @thedatetimeoffset AS 'datetimeoffset', @thedatetime2 AS 'datetime2';
Result:
+------------------------------------+-----------------------------+ | datetimeoffset | datetime2 | |------------------------------------+-----------------------------| | 2025-05-21 10:15:30.5555555 +07:30 | 2025-05-21 10:15:30.5555555 | +------------------------------------+-----------------------------+
Here, I set a datetime2 variable to the same value as the datetimeoffset variable. This causes the value to be converted to datetime2 and we can then use a SELECT statement to see the value of each variable.
Both variables use a scale of 7, which means they have 7 decimal places.
So in this case, the only difference between the two is that the datetimeoffset value includes the time zone offset and the datetime2 value doesn’t.
Example 2 – Changing the Precision
Both types allow you to specify a precision (by using a scale between 0 and 7). Therefore, it’s possible to set the datetime2 value to a lower precision than the datetimeoffset value (and vice versa).
Example:
DECLARE @thedatetimeoffset datetimeoffset(7), @thedatetime2 datetime2(3); SET @thedatetimeoffset = '2025-05-21 10:15:30.5555555 +07:30'; SET @thedatetime2 = @thedatetimeoffset; SELECT @thedatetimeoffset AS 'datetimeoffset', @thedatetime2 AS 'datetime2';
Result:
+------------------------------------+-------------------------+ | datetimeoffset | datetime2 | |------------------------------------+-------------------------| | 2025-05-21 10:15:30.5555555 +07:30 | 2025-05-21 10:15:30.556 | +------------------------------------+-------------------------+
Here I set the datetime2 value to a scale of 3, which means it ends up with 3 decimal places instead of 7. In this case, its fractional seconds are rounded up (because the next fractional digit is 5 or higher).
So we can see that it’s possible to get a different date/time value depending on the fractional seconds that we assign to datetime2. This also works going the other way (e.g. if we convert from datetime2(7) to datetimeoffset(3)).
However, if we reduce the fractional part, no rounding is performed:
DECLARE @thedatetimeoffset datetimeoffset(7), @thedatetime2 datetime2(3); SET @thedatetimeoffset = '2025-05-21 10:15:30.5554444 +07:30'; SET @thedatetime2 = @thedatetimeoffset; SELECT @thedatetimeoffset AS 'datetimeoffset', @thedatetime2 AS 'datetime2';
Result:
+------------------------------------+-------------------------+ | datetimeoffset | datetime2 | |------------------------------------+-------------------------| | 2025-05-21 10:15:30.5554444 +07:30 | 2025-05-21 10:15:30.555 | +------------------------------------+-------------------------+
Example 3 – Setting Values from String Literals
In the previous examples, the datetime2 value was assigned by setting it to the same value as the datetimeoffset value. When we do that, SQL Server performs an implicit conversion in order for the data to “fit” the new data type.
We can also assign the same value directly to the datetime2 variable (even though the official documentation doesn’t explicitly state that it accepts a string literal with a time zone offset):
DECLARE @thedatetimeoffset datetimeoffset(7), @thedatetime2 datetime2(7); SET @thedatetimeoffset = '2025-05-21 10:15:30.5555555 +07:30'; SET @thedatetime2 = '2025-05-21 10:15:30.5555555 +07:30'; SELECT @thedatetimeoffset AS 'datetimeoffset', @thedatetime2 AS 'datetime2';
Result:
+------------------------------------+-----------------------------+ | datetimeoffset | datetime2 | |------------------------------------+-----------------------------| | 2025-05-21 10:15:30.5555555 +07:30 | 2025-05-21 10:15:30.5555555 | +------------------------------------+-----------------------------+
Example 4 – Storage Size
The datetime2 data type uses two bytes less storage than datetimeoffset for any given precision.
The datetime2 can be either 6, 7, or 8 bytes, depending on its precision.
The datetimeoffset can be either 8, 9, or 10 bytes, depending on its precision.
Microsoft states that the datetime2 type also uses 1 extra byte in order to store its precision, in which case it would use at least 3 bytes more than smalldatetime.
This also applies to datetimeoffset (even though it’s not explicitly stated in the Microsoft documentation).
However, that depends on whether we’re storing it in a table or in a variable, and whether or not we’re converting it to a binary constant.
Here’s what happens if we use the DATALENGTH() function to return the number of bytes used for each of our values:
DECLARE @thedatetimeoffset datetimeoffset(7), @thedatetime2 datetime2(7); SET @thedatetimeoffset = '2025-05-21 10:15:30.5555555 +07:30'; SET @thedatetime2 = @thedatetimeoffset; SELECT DATALENGTH(@thedatetimeoffset) AS 'datetimeoffset', DATALENGTH(@thedatetime2) AS 'datetime2';
Result
+------------------+-------------+ | datetimeoffset | datetime2 | |------------------+-------------| | 10 | 8 | +------------------+-------------+
As expected, 10 bytes for datetimeoffset and 8 bytes for datetime2.
But if we convert them to varbinary, we get the following:
DECLARE @thedatetimeoffset datetimeoffset(7), @thedatetime2 datetime2(7); SET @thedatetimeoffset = '2025-05-21 10:15:30.5555555 +07:30'; SET @thedatetime2 = @thedatetimeoffset; SELECT DATALENGTH(CAST(@thedatetimeoffset AS varbinary(16))) AS 'datetimeoffset', DATALENGTH(CAST(@thedatetime2 AS varbinary(16))) AS 'datetime2';
Result
+------------------+-------------+ | datetimeoffset | datetime2 | |------------------+-------------| | 11 | 9 | +------------------+-------------+
An extra byte is added to each value in order to store the precision.
Many developers assume that converting to varbinary is representative of how SQL Server actually stores date and time values. However this is only partially true.
While it’s true that SQL Server stores its date and time values in hexadecimal, that hex value doesn’t actually include the precision. This is because the precision is included in the column definition. But when we convert to varbinary like we did in the previous example, the precision is prepended, and this adds an extra byte.
For more detail on how these data types are stored in different contexts, see the following articles: