This article contains examples of converting a date value to a datetimeoffset value in SQL Server.
When you convert a date value to datetimeoffset, extra information is added to the value. This is because the datetimeoffset data type contains both date and time information, as well as the time offset information. In other words, the datetimeoffset data type defines a date that is combined with a time of a day that has time zone awareness and is based on a 24-hour clock. The date data type, on the other hand, only contains date information.
When we convert from date to datetimeoffset, the time (and time zone offset) is automatically added to the value. However, you can always change the value if required (including the time zone offset).
The datetimeoffset data type also allows you to specify the fractional seconds precision. If you don’t specify this, it uses a scale of 7. This means it will include 7 digits on the right side of the decimal point.
Example 1 – Implicit Conversion
Here’s an example of an implicit conversion between date and datetimeoffset.
DECLARE @thedate date, @thedatetimeoffset datetimeoffset(7) SET @thedate = '2020-12-01' SET @thedatetimeoffset = @thedate SELECT @thedate AS 'date', @thedatetimeoffset AS 'datetimeoffset';
Result:
+------------+------------------------------------+ | date | datetimeoffset | |------------+------------------------------------| | 2020-12-01 | 2020-12-01 00:00: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 date value to a datetimeoffset variable.
We can see that the date variable only contains date information, whereas, the datetimeoffset variable contains date, time, and time zone offset information.
When you convert between date and datetimeoffset(7) (i.e. using a scale of 7), the time component is set to 00:00:00.0000000 +00:00. This is also the default value, so you can omit the precision value and it will use a scale of 7 (which results in a precision of 34). You can reduce the precision if you prefer. Reducing the precision can also reduce the amount of space required to store the value.
Just to be clear, scale is the number of digits to the right of the decimal point in a number. Precision is the total number of digits in the number.
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 date and datetimeoffset.
DECLARE @thedate date SET @thedate = '2020-12-01' SELECT @thedate AS 'date', CAST(@thedate AS datetimeoffset) AS 'datetimeoffset';
Result:
+------------+------------------------------------+ | date | datetimeoffset | |------------+------------------------------------| | 2020-12-01 | 2020-12-01 00:00:00.0000000 +00:00 | +------------+------------------------------------+
So we get the same result as the implicit conversion.
We can also adjust the time like this:
DECLARE @thedate date SET @thedate = '2020-12-01' SELECT @thedate AS 'date', DATEADD(hour, 7, CAST(@thedate AS datetimeoffset)) AS 'datetimeoffset';
Result:
+------------+------------------------------------+ | date | datetimeoffset | |------------+------------------------------------| | 2020-12-01 | 2020-12-01 07:00:00.0000000 +00:00 | +------------+------------------------------------+
Note that you can’t use the DATEADD() function to modify the time offset component. But don’t worry, there is a way to modify it (read on to find out how).
Example 3 – Explicit Conversion using CONVERT()
Here’s an example of an explicit conversion using the CONVERT() function instead of CAST().
DECLARE @thedate date SET @thedate = '2020-12-01' SELECT @thedate AS 'date', CONVERT(datetimeoffset, @thedate) AS 'datetimeoffset';
Result:
+------------+------------------------------------+ | date | datetimeoffset | |------------+------------------------------------| | 2020-12-01 | 2020-12-01 00:00:00.0000000 +00:00 | +------------+------------------------------------+
And adjusting the time:
DECLARE @thedate date SET @thedate = '2020-12-01' SELECT @thedate AS 'date', DATEADD(hour, 7, CONVERT(datetimeoffset, @thedate)) AS 'datetimeoffset';
Result:
+------------+------------------------------------+ | date | datetimeoffset | |------------+------------------------------------| | 2020-12-01 | 2020-12-01 07:00:00.0000000 +00:00 | +------------+------------------------------------+
Example 4 – Modify the Time Offset
In the previous examples we used the DATEADD() function to modify the time value. This function can be used to modify any part of the date or time component, except for the time offset component.
If you need to modify the time offset, you can use the TODATETIMEOFFSET() function. You can also use this function to convert the original date value to a datetimeoffset value. This function accepts a date value (that can resolve to a datetime2 value), and an offset value.
Here’s an example:
DECLARE @thedate date, @thedatetimeoffset datetimeoffset SET @thedate = '2020-12-01' SET @thedatetimeoffset = TODATETIMEOFFSET(@thedate, '+07:00') SELECT @thedate AS 'date', @thedatetimeoffset AS 'datetimeoffset';
Result:
+------------+------------------------------------+ | date | datetimeoffset | |------------+------------------------------------| | 2020-12-01 | 2020-12-01 00:00:00.0000000 +07:00 | +------------+------------------------------------+
And here’s an example using the function within the SELECT statement:
DECLARE @thedate date = '2020-12-01' SELECT @thedate AS 'date', TODATETIMEOFFSET(@thedate, '+07:00') AS 'datetimeoffset';
Result:
+------------+------------------------------------+ | date | datetimeoffset | |------------+------------------------------------| | 2020-12-01 | 2020-12-01 00:00:00.0000000 +07:00 | +------------+------------------------------------+
The TODATETIMEOFFSET() function also accepts a datetimeoffset value as its first parameter, so you can also use it to modify existing datetimeoffset values if required.
Example:
DECLARE @thedate date, @thedatetimeoffset datetimeoffset(7) SET @thedate = '2020-12-01' SET @thedatetimeoffset = @thedate SELECT @thedate AS 'date', @thedatetimeoffset AS 'datetimeoffset', TODATETIMEOFFSET(@thedatetimeoffset, '+07:00') AS 'Modified';
Result:
+------------+------------------------------------+------------------------------------+ | date | datetimeoffset | Modified | |------------+------------------------------------+------------------------------------| | 2020-12-01 | 2020-12-01 00:00:00.0000000 +00:00 | 2020-12-01 00:00:00.0000000 +07:00 | +------------+------------------------------------+------------------------------------+