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

When you convert a smalldatetime value to datetimeoffset, the smalldatetime value is copied to the datetimeoffset value. The fractional seconds are set to 0, and the time zone offset is set to +00:0.

The smalldatetime data type doesn’t have any fractional seconds, and its seconds component is always set to zero (:00). Its accuracy is to the nearest minute.

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, smalldatetime has no time zone awareness, so there are no existing values to preserve. In this case, the time zone offset is set 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, see below for my comments and some examples regarding this option.

Example 1 – Implicit Conversion

First, here’s an example of an implicit conversion between smalldatetime and datetimeoffset.

DECLARE 
  @thesmalldatetime smalldatetime, 
  @thedatetimeoffset datetimeoffset(7);
SET @thesmalldatetime = '2025-05-21 10:15:30';
SET @thedatetimeoffset = @thesmalldatetime;
SELECT 
  @thesmalldatetime AS 'smalldatetime',
  @thedatetimeoffset AS 'datetimeoffset(7)';

Result:

+---------------------+------------------------------------+
| smalldatetime       | datetimeoffset(7)                  |
|---------------------+------------------------------------|
| 2025-05-21 10:16:00 | 2025-05-21 10:16:00.0000000 +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 smalldatetime value to a datetimeoffset variable.

We can see that the datetimeoffset variable has a fractional part (0000000), whereas the smalldatetime value has no fractional part, and its minutes were rounded up when assigning the initial value to it. The datetimeoffset value also includes a time zone offset of +00:00.

Using 7 fractional seconds precision causes datetimeoffset to use 11 bytes for storage (10 bytes to store the data, 1 byte for the precision). In comparison, smalldatetime uses only 4 bytes. However, you can reduce the precision of the datetimeoffset value by replacing the 7 with a lower number. If you want to remove the fractional seconds part altogether, simply use datetimeoffset(0). Doing this will reduce the storage size to 9 bytes (including 1 for the precision).

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

DECLARE @thesmalldatetime smalldatetime;
SET @thesmalldatetime = '2025-05-21 10:15:30.125';
SELECT 
  @thesmalldatetime AS 'smalldatetime',
  CAST(@thesmalldatetime AS datetimeoffset(7)) AS 'datetimeoffset(7)';

Result:

+---------------------+------------------------------------+
| smalldatetime       | datetimeoffset(7)                  |
|---------------------+------------------------------------|
| 2025-05-21 10:16:00 | 2025-05-21 10:16:00.0000000 +00:00 |
+---------------------+------------------------------------+

Example 3 – Explicit Conversion using CONVERT()

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

DECLARE @thesmalldatetime smalldatetime;
SET @thesmalldatetime = '2025-05-21 10:15:30.125';
SELECT 
  @thesmalldatetime AS 'smalldatetime',
  CONVERT(datetimeoffset(7), @thesmalldatetime) AS 'datetimeoffset(7)';

Result:

+---------------------+------------------------------------+
| smalldatetime       | datetimeoffset(7)                  |
|---------------------+------------------------------------|
| 2025-05-21 10:16:00 | 2025-05-21 10:16:00.0000000 +00:00 |
+---------------------+------------------------------------+

Example 4 – Changing the Time Zone Offset

The fact that you’re converting your smalldatetime values to datetimeoffset means that you’re probably doing it for the timezone offset. And it’s likely that you want it set to a different offset than +00:00 (the default offset).

Fortunately, you can use the TODATETIMEOFFSET() function to change the offset.

You can also use this function to convert the original smalldatetime 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 @thesmalldatetime smalldatetime, @thedatetimeoffset datetimeoffset;
SET @thesmalldatetime = '2025-05-21 10:15:30';
SET @thedatetimeoffset = TODATETIMEOFFSET(@thesmalldatetime, '+07:00');
SELECT 
  @thesmalldatetime AS 'smalldatetime',
  @thedatetimeoffset AS 'datetimeoffset';

Result:

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

And here’s an example using the function within the SELECT statement:

DECLARE @thesmalldatetime smalldatetime = '2025-05-21 10:15:30';
SELECT 
  @thesmalldatetime AS 'smalldatetime',
  TODATETIMEOFFSET(@thesmalldatetime, '+07:00') AS 'datetimeoffset';

Result:

+---------------------+------------------------------------+
| smalldatetime       | datetimeoffset                     |
|---------------------+------------------------------------|
| 2025-05-21 10:16:00 | 2025-05-21 10:16:00.0000000 +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 smalldatetime argument, which has no fractional seconds.

My system returns trailing zeros with datetimeoffset fractional part, however, you might see something like this:

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

Either way, you can still take full advantage of the datetimeoffset data type’s precision if you need to modify the value later.

Here’s an example that uses the DATEADD() function to change the fractional seconds after the conversion has already been done.

DECLARE @thesmalldatetime smalldatetime, @thedatetimeoffset datetimeoffset(7);
SET @thesmalldatetime = '2025-05-21 10:15:30';
SET @thedatetimeoffset = TODATETIMEOFFSET(@thesmalldatetime, '+07:00');
SELECT 
  @thesmalldatetime AS 'smalldatetime',
  @thedatetimeoffset AS 'datetimeoffset',
  DATEADD(nanosecond, 123456700, @thedatetimeoffset) AS 'Modified';

Result (using vertical output):

smalldatetime  | 2025-05-21 10:16:00
datetimeoffset | 2025-05-21 10:16:00.0000000 +07:00
Modified       | 2025-05-21 10:16:00.1234567 +07:00