datetime vs datetimeoffset in SQL Server: What’s the Difference?

Posted on by Ian

This article highlights the main differences between the datetime and datetimeoffset data types in SQL Server.

Both data types are used for storing date and time values. But there are significant differences between the two.

Perhaps the most obvious difference is that the datetimeoffset stores the time zone offset, whereas datetime doesn’t.

Another important difference is that datetimeoffset allows you to specify the precision (up to 7 decimal places). This means that datetimeoffset values can vary in their storage size, depending on the precision being used.

The datetime type on the other hand, has a fixed storage size and precision.

Generally, you should avoid using datetime unless you have a good reason to use it (such as supporting a legacy system). Also, the datetime2 type is a closer match than datetimeoffset, so you’re better off using that if you don’t need a time zone offset.

Either way, here’s a table that compares datetime and datetimeoffset:

Feature datetimeoffset datetime
SQL Compliant (ANSI & ISO 8601) Yes No
Date Range 0001-01-01 through 9999-12-31 1753-01-01 through 9999-12-31
Time Range 00:00:00 through 23:59:59.9999999 00:00:00 through 23:59:59.997
Character Length 26 positions minimum
34 maximum 		19 positions minimum
23 maximum
Storage Size 8 to 10 bytes, depending on the precision*

* Plus 1 byte to store the precision in some cases. See below for more info.

 8 bytes
Accuracy 100 nanoseconds Rounded to increments of .000, .003, or .007 seconds
User-defined fractional second precision Yes No
Time zone offset range -14:00 through +14:00 None
Time zone offset aware and preservation Yes No
Daylight saving aware No No

Example 1 – Basic Comparison

In any case, here’s a quick example to demonstrate the basic difference between datetime and datetimeoffset.

DECLARE 
  @thedatetimeoffset datetimeoffset(7), 
  @thedatetime datetime;
SET @thedatetimeoffset = '2025-05-21 10:15:30.5555555 +07:30';
SET @thedatetime = @thedatetimeoffset;
SELECT 
  @thedatetimeoffset AS 'datetimeoffset',
  @thedatetime AS 'datetime';

Result:

+------------------------------------+-------------------------+
| datetimeoffset                     | datetime                |
|------------------------------------+-------------------------|
| 2025-05-21 10:15:30.5555555 +07:30 | 2025-05-21 10:15:30.557 |
+------------------------------------+-------------------------+

Here, I set a datetime variable to the same value as the datetimeoffset variable. This causes the value to be converted to datetime and we can then use a SELECT statement to see the value of each variable.

In this case, the datetimeoffset value includes the time zone offset and 7 decimal places. The datetime value on the other hand, doesn’t include the time zone offset and it only has 3 decimal places. Furthermore, its third fractional digit is rounded up. This is because its accuracy is always rounded to increments of .000, .003, or .007 seconds.

Example 2 – Setting Values from String Literals

In the previous example, the datetime 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.

If we try to assign the same value directly to the datetime variable we get an error:

DECLARE 
  @thedatetimeoffset datetimeoffset(7), 
  @thedatetime datetime;
SET @thedatetimeoffset = '2025-05-21 10:15:30.5555555 +07:30';
SET @thedatetime = '2025-05-21 10:15:30.5555555 +07:30';
SELECT 
  @thedatetimeoffset AS 'datetimeoffset',
  @thedatetime AS 'datetime';

Result:

Msg 241, Level 16, State 1, Line 5
Conversion failed when converting date and/or time from character string.

This is because the datetime data type doesn’t support a string literal with a time zone offset. Also, it doesn’t support string literals with more than 3 decimal places.

So if we remove the time zone offset, but keep all the fractional seconds, we’ll still get an error:

DECLARE 
  @thedatetimeoffset datetimeoffset(7), 
  @thedatetime datetime;
SET @thedatetimeoffset = '2025-05-21 10:15:30.5555555 +07:30';
SET @thedatetime = '2025-05-21 10:15:30.5555555';
SELECT 
  @thedatetimeoffset AS 'datetimeoffset',
  @thedatetime AS 'datetime';

Result:

Msg 241, Level 16, State 1, Line 5
Conversion failed when converting date and/or time from character string.

To make it work, we’d need to assign a value with no more than 3 decimal places:

DECLARE 
  @thedatetimeoffset datetimeoffset(7), 
  @thedatetime datetime;
SET @thedatetimeoffset = '2025-05-21 10:15:30.5555555 +07:30';
SET @thedatetime = '2025-05-21 10:15:30.555';
SELECT 
  @thedatetimeoffset AS 'datetimeoffset',
  @thedatetime AS 'datetime';

Result:

+------------------------------------+-------------------------+
| datetimeoffset                     | datetime                |
|------------------------------------+-------------------------|
| 2025-05-21 10:15:30.5555555 +07:30 | 2025-05-21 10:15:30.557 |
+------------------------------------+-------------------------+

Either way, datetime will always hold a different value to datetimeoffset, because it doesn’t include the timezone offset. This will be true even if we use the same fractional seconds precision and fractional seconds value.

To demonstrate this, here’s what happens if we assign the same value to datetimeoffset:

DECLARE 
  @thedatetimeoffset datetimeoffset(3), 
  @thedatetime datetime;
SET @thedatetimeoffset = '2025-05-21 10:15:30.123';
SET @thedatetime = '2025-05-21 10:15:30.123';
SELECT 
  @thedatetimeoffset AS 'datetimeoffset',
  @thedatetime AS 'datetime';

Result:

+------------------------------------+-------------------------+
| datetimeoffset                     | datetime                |
|------------------------------------+-------------------------|
| 2025-05-21 10:15:30.1230000 +00:00 | 2025-05-21 10:15:30.123 |
+------------------------------------+-------------------------+

In this case datetimeoffset uses a scale of 3, which gives it 3 decimal places (the same as datetime). This is done using datetimeoffset(3) when declaring the variable.

I also changed the fractional seconds so that datetime wouldn’t round them up (so that both values share exactly the same fractional part).

Regardless, datetimeoffset still adds a time zone offset, set to its default value of +00:00.

Note that my system displays trailing zeros on datetimeoffset‘s fractional part, but the value only uses 3 decimal places.

Example 3 – Storage Size

The datetime data type uses 8 bytes.

The datetimeoffset data type uses either 8, 9, or 10 bytes, depending on its precision.

Therefore, you’re not saving any storage size by using datetime.

However, if you convert a datetimeoffset value to a binary constant, it does add 1 byte in order to store precision.

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), 
  @thedatetime datetime;
SET @thedatetimeoffset = '2025-05-21 10:15:30.5555555 +07:30';
SET @thedatetime = @thedatetimeoffset;
SELECT 
  DATALENGTH(@thedatetimeoffset) AS 'datetimeoffset',
  DATALENGTH(@thedatetime) AS 'datetime';

Result

+------------------+------------+
| datetimeoffset   | datetime   |
|------------------+------------|
| 10               | 8          |
+------------------+------------+

As expected, 10 bytes for datetimeoffset and 8 bytes for datetime.

But if we convert them to varbinary, we get the following:

DECLARE 
  @thedatetimeoffset datetimeoffset(7), 
  @thedatetime datetime;
SET @thedatetimeoffset = '2025-05-21 10:15:30.5555555 +07:30';
SET @thedatetime = @thedatetimeoffset;
SELECT 
  DATALENGTH(CAST(@thedatetimeoffset AS varbinary(16))) AS 'datetimeoffset',
  DATALENGTH(CAST(@thedatetime AS varbinary(16))) AS 'datetime';

Result

+------------------+------------+
| datetimeoffset   | datetime   |
|------------------+------------|
| 11               | 8          |
+------------------+------------+

An extra byte is added to the datetimeoffset value but not to the datetime value. This is because the datetimeoffset value needs an extra byte to store the precision (because the precision is user-defined). The datetime value on the other hand has a fixed precision, so there’s no need for the precision to be stored with the value.

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 when storing datetimeoffset values. This is because the precision is included in the column definition.

For more detail on how this data type is stored in the database, see Understanding ‘datetimeoffset’ Storage Size in SQL Server.

Should I use ‘datetime’ or ‘datetimeoffset’?

If you need to include a time zone offset, then you’ll need to use datetimeoffset. If not, then datetime may suffice.

However, Microsoft recommends that you use datetime2 for new work, as it has many benefits over datetime.

See datetime vs datetime2 for a comparison on these data types.