If you have a datetimeoffset value, but you don’t need the time and time zone offset part, converting it to date will save you a lot of storage space (while removing unnecessary details from the value). This article contains examples of converting a datetimeoffset value to a date value in SQL Server.

The datetimeoffset data type includes the date and time with a time zone offset. It also has a fractional seconds part between 0 and 7 (this depends on how many fractional seconds are assigned to it). This is done by using the datetimeoffset(n) syntax. If you don’t specify this, it will use 7 (the default). This data type’s storage size is either 8, 9, or 10 bytes (plus 1 byte to store the precision), depending on the precision being used. Its accuracy is 100 nanoseconds.

The date data type on the other hand, only includes the date. It doesn’t include the time, and it has an accuracy of 1 day. It uses 3 bytes for storage.

When you convert a datetimeoffset value to a date data type, you lose the time portion (including the time zone offset). However, you also reduce the storage size down to a fixed 3 bytes. Of course, you would only do this conversion if you don’t need the time portion and time zone offset.

Example 1 – Implicit Conversion

Here’s an example of an implicit conversion between datetimeoffset and date.

DECLARE 
  @thedatetimeoffset datetimeoffset, 
  @thedate date;
SET @thedatetimeoffset = '2025-05-21 10:15:30.1234567 +10:30';
SET @thedate = @thedatetimeoffset;
SELECT 
  @thedatetimeoffset AS 'datetimeoffset',
  @thedate AS 'date';

Result:

+------------------------------------+------------+
| datetimeoffset                     | date       |
|------------------------------------+------------|
| 2025-05-21 10:15:30.1234567 +10:30 | 2025-05-21 |
+------------------------------------+------------+

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 datetimeoffset value to a date variable.

Here we can see that the date value only includes the date (without the time component). The time component, including its time zone offset, has been removed from the value.

In this example, the datetimeoffset value uses the default precision (which results in 7 decimal places). This results in the data type using 10 bytes (actually, 11 bytes if you count the extra byte that stores the precision). The date value uses just 3 bytes.

Example 2 – 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 datetimeoffset and date.

DECLARE @thedatetimeoffset datetimeoffset;
SET @thedatetimeoffset = '2025-05-21 10:15:30.1234567 +10:30';
SELECT 
  @thedatetimeoffset AS 'datetimeoffset',
  CAST(@thedatetimeoffset AS date) AS 'date'; 

Result:

+------------------------------------+------------+
| datetimeoffset                     | date       |
|------------------------------------+------------|
| 2025-05-21 10:15:30.1234567 +10:30 | 2025-05-21 |
+------------------------------------+------------+

Example 3 – Explicit Conversion using CONVERT()

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

DECLARE @thedatetimeoffset datetimeoffset;
SET @thedatetimeoffset = '2025-05-21 10:15:30.1234567 +10:30';
SELECT 
  @thedatetimeoffset AS 'datetimeoffset',
  CONVERT(date, @thedatetimeoffset) AS 'date';

Result:

+------------------------------------+------------+
| datetimeoffset                     | date       |
|------------------------------------+------------|
| 2025-05-21 10:15:30.1234567 +10:30 | 2025-05-21 |
+------------------------------------+------------+