SQL - TODATETIMEOFFSET() Function

The SQL TODATETIMEOFFSET() function is a Date and Time Function that uses the user-specified datetime2 and time zone to return a datetimeoffset value.

The TODATETIMEOFFSET function operates two inputs − a target time zone offset and a nonoffset date and time value, and it simply combines the two to produce a DATETIMEOFFSET value. The output of this function is a value of the data type datetimeoffset.

We can provide the time zone in the format of minutes or hours. If we provide in minutes, we use an integer (-4); if we provide in hours, we use a string ('+06.00'). The range for the time zone is +14 to -14 (hours).

Syntax

Following is the syntax of the SQL TODATETIMEOFFSET() function −

TODATETIMEOFFSET(expression,time_zone)

Parameters

This function accepts only two parameter. The same is described below −

  • expression − that to be resolved into datetime2 value.

  • time_zone − it is a timezone offset or signed integer range from +14 to -14.

Example

In the following example, we are going to change the timezone offset of minutes by using the following query −

In this example, we are going to change current time zone to -150minutes;

SELECT TODATETIMEOFFSET (GETDATE(), -150) AS Result;

Output

When we execute the above query, the output is obtained as follows −

+--------------------------------+
| Result                         |
+--------------------------------+
| 2023-02-20 12:52:55.827 -02:30 |
+--------------------------------+

Example

Let us look into in the another scenario, where we are going to change the time zone using minutes in positive by using the following query −

SELECT TODATETIMEOFFSET (GETDATE(), 360) AS Result;

Output

When the query, gets executed it will generate the output as shown below −

+--------------------------------+
| Result                         |
+--------------------------------+
| 2023-02-20 15:22:33.533 +06:00 |
+--------------------------------+

Example

In the following example, we are using going to set variable explicitly with a datetime2 value, and applyingTODATETIMEOFFSET() to that value and comparing both the values by using the following query −

DECLARE @DATE datetime2 = '2023-02-20 02:33:18.0000000';
SELECT @DATE AS 'Original Date',
   TODATETIMEOFFSET( @DATE, '+06:00' ) AS '+06:00';

Output

On executing the above query, the output is displayed as follows − 

+-----------------------------+-------------------------------------+
| Original Date               | +06:00                              |
+-----------------------------+-------------------------------------+
| 2023-02-20 02:33:18.0000000 | 2023-02-20 02:33:18.0000000 +06:00  |
+-----------------------------+-------------------------------------+

Example

Let us look into another example, where we are going to set the timezone offset as integer by using the following query −

SELECT TODATETIMEOFFSET( '2023-02-20 02:33:18.0000000', -150 ) AS Result;

Output

The output for the above query is produced as given below −

+------------------------------------+
| Result                             |
+------------------------------------+
| 2023-02-20 02:33:18.0000000 -02:30 |
+------------------------------------+

Example

Let us look into the following example where we are using the SYSDATETIMEOFFSET() function as a date expression, which generates the current date/time of the system that SQL server running by using the following query −

SELECT SYSDATETIMEOFFSET() AS 'Current Date',
   TODATETIMEOFFSET( SYSDATETIMEOFFSET(), '+06:00' ) AS '+06:00';

Output

When the query gets executed, it will generate an output as shown below −

+------------------------------------+------------------------------------+
| Current Date                       | +06:00                             |
+------------------------------------+------------------------------------+
| 2023-02-20 14:50:06.8050290 +05:30 | 2023-02-20 14:50:06.8050290 +06:00 |
+------------------------------------+------------------------------------+

Example

In the following example, we are using the DATETIMEOFFSET() function and going to retrieve the offset value using the TODATETIMEOFFSET() function by using the following query −

DECLARE @DATE DATETIMEOFFSET = '2023-02-20 14:50:06.8050290 +04:30';
SELECT @DATE AS 'Original Date',
   TODATETIMEOFFSET( @DATE, '+06:00' ) AS '+06:00';

Output

On running the above query, it will generate the output as shown below −

+------------------------------------+------------------------------------+
| Original Date                      | +06:00                             |
+------------------------------------+------------------------------------+
| 2023-02-20 14:50:06.8050290 +04:30 | 2023-02-20 14:50:06.8050290 +06:00 |
+------------------------------------+------------------------------------+

Example

In the following example, we are using the DATETIMEOFFSET() function and going to retrieve the offset value using the TODATETIMEOFFSET() function by using the following query −

DECLARE @DATE DATETIMEOFFSET = '2023-02-20 14:50:06.8050290 +04:30';
SELECT @DATE AS 'Original Date',
   TODATETIMEOFFSET( @DATE, '+06:00' ) AS '+06:00';

Output

On running the above query, it will generate the output as shown below −

+------------------------------------+------------------------------------+
| Original Date                      | +06:00                             |
+------------------------------------+------------------------------------+
| 2023-02-20 14:50:06.8050290 +04:30 | 2023-02-20 14:50:06.8050290 +06:00 |
+------------------------------------+------------------------------------+

Example

Look at the following example, where we are going to change the timezone using hours by running the following query −

SELECT TODATETIMEOFFSET (GETDATE(), '+05:30') AS Result;

Output

If we execute the above query, the result is produced as follows −

+--------------------------------+
| Result                         |
+--------------------------------+
| 2023-02-20 15:12:41.213 +05:30 |
+--------------------------------+

Example

Here is another scenario where we are going to change the time zone using hours in negative by using the following query −

SELECT TODATETIMEOFFSET (GETDATE(), '-06:00') AS Result;

Output

On executing the above query, it will generate the following output as shown below −

+---------------------------------+
| Result                          |
+---------------------------------+
| 2023-02-20 15:16:30.247 -06:00  |
+---------------------------------+
sql-date-functions.htm
Advertisements