This article contains examples of converting a datetimeoffset value to smalldatetime in SQL Server.

When you convert a datetimeoffset value to smalldatetime, the date and hours are copied. The minutes are rounded up (depending on the seconds value) and the seconds are set to 0.

The datetimeoffset data type allows you to specify a fractional seconds precision from 0 to 7. This is done by using the datetimeoffset(n) syntax. If you don’t specify this, it will use 7 (the default). It also has a time zone offset. This data type’s storage size is either 8, 9, or 10 bytes (plus 1 byte for the precision), depending on the precision being used.

The smalldatetime data type on the other hand, doesn’t have time zone awareness, and therefore, it doesn’t include any time zone offset. It also doesn’t have any fractional seconds, and its seconds component is always set to zero (:00). Its accuracy is to the nearest minute. This data type’s storage size is fixed at 4 bytes.

Example 1 – Implicit Conversion

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

DECLARE 
  @thedatetimeoffset datetimeoffset, 
  @thesmalldatetime smalldatetime;
SET @thedatetimeoffset = '2025-05-21 10:15:30.1234567 +07:00';
SET @thesmalldatetime = @thedatetimeoffset;
SELECT 
  @thedatetimeoffset AS 'datetimeoffset',
  @thesmalldatetime AS 'smalldatetime';

Result:

+------------------------------------+---------------------+
| datetimeoffset                     | smalldatetime       |
|------------------------------------+---------------------|
| 2025-05-21 10:15:30.1234567 +07:00 | 2025-05-21 10:16: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 datetimeoffset value to a smalldatetime variable.

We can see that the smalldatetime variable has no fractional seconds precision, and its seconds have been set to zero. Also, the minutes have been rounded up, due to the fact that the original value had a seconds value of 30.

Another observation is that the time zone offset was truncated – the smalldatetime data type has no time zone awareness.

In this conversion, the storage size has decreased from 10 bytes (11 bytes when you count the precision) for datetimeoffset, to 4 bytes for smalldatetime.

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

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

Result:

+------------------------------------+-------------------------+
| datetimeoffset                     | smalldatetime           |
|------------------------------------+-------------------------|
| 2025-05-21 10:15:30.1234567 +07:00 | 2025-05-21 10:16:00     |
+------------------------------------+-------------------------+

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 +07:00';
SELECT 
  @thedatetimeoffset AS 'datetimeoffset',
  CONVERT(smalldatetime, @thedatetimeoffset) AS 'smalldatetime';

Result:

+------------------------------------+-------------------------+
| datetimeoffset                     | smalldatetime           |
|------------------------------------+-------------------------|
| 2025-05-21 10:15:30.1234567 +07:00 | 2025-05-21 10:16:00     |
+------------------------------------+-------------------------+