DATETIMEOFFSET SQL Server

The DATETIMEOFFSET can manipulate a single point in time value (DateTime Value) with the offset value. The offset specifies the value of how much time it differs from the UTC (Universal Time Coordinate).

This tutorial covers how to manipulate the DateTime data type. Datetime is commonly used in applications that deal with date and time, but what if you need to add or subtract hours or even a whole day from your result set? For DateTime manipulation, SQL has the datetimeoffset data type (also known as DATETIME2), a particular data type that allows users to join or use time literals as a part of their query expression.

Syntax:

DATETIMEOFFSET [ (fractional seconds precision) ]

We can declare a variable or table column for the DATETIMEOFFSET.

Example: DATETIMEOFFSET SQL as variable

DECLARE @datetimeoffset datetimeoffset(4) = '12-10-25 12:32:10 +01:00';  
SELECT @datetimeoffset

Example: DATETIMEOFFSET SQL as a table column

CREATE TABLE DemoDataTable(
   ID int, 
   created_at DATETIMEOFFSET NOT NULL
);
INSERT INTO DemoDataTable VALUES (1, '2021-12-08 01:00:00.0000000 -08:00')
SELECT * FROM DemoDataTable

Time zone offset in DATETIMEOFFSET SQL

The representation to define the time zone offset is [+|-] hh:mm for a time or datetime value.

• hh refers to digits 00 to 14 for representing the number of hours in the time zone offset.

• mm range is from 00 to 59 and represents the additional minutes in the time zone offset

• You need to use the mandatory sign + or – for time zone offset. It represents whether the time zone offset will be added or subtracted from the UTC zone to get the local time. Its valid value is -14:00 to +14:00

Converting datetimeoffset SQL data type to other date and time types

The following SQL query converts the datetimeoffset(4) to a date value.

DECLARE @datetimeoffset datetimeoffset(4) = '12-09-21 11:00:09 +01:00';  
DECLARE @date date= @datetimeoffset;  
SELECT @datetimeoffset AS '@datetimeoffset ', @date AS 'date';  

In the following example, we covert the value of datetimeoffset(4) to a time(3) value.

Conversion from a datetimeoffset(4) value to a DateTime value.

DECLARE @datetimeoffset datetimeoffset(4) = '12-09-21 11:00:09 +01:00';  
DECLARE @datetime datetime = @datetimeoffset;   
SELECT @datetimeoffset AS '@datetimeoffset ', @datetime AS 'datetime';  

Covert from datetimeoffset(4) to a smalldatetime value.

DECLARE @datetimeoffset datetimeoffset(4) = '12-09-21 11:00:09 +01:00';  
DECLARE @smalldatetime smalldatetime = @datetimeoffset;    
SELECT @datetimeoffset AS '@datetimeoffset', @smalldatetime AS '@smalldatetime'; 
  

Covert from datetimeoffset(4) to a datetime2 value.

DECLARE @datetimeoffset datetimeoffset(4) = '12-09-21 11:00:09 +01:00';  
DECLARE @datetime2 datetime2(3)=@datetimeoffset;   
SELECT @datetimeoffset AS '@datetimeoffset', @datetime2 AS '@datetime2';  

Let's use the below example to convert a single datetimeoffset value into different formats.

DECLARE @Val1 datetimeoffset ='2021-01-01 01:15:19. 1234567 +12:15'
SELECT   
    CAST(@Val1 AS time(7)) AS 'time'   
   ,CAST(@Val1 AS date) AS 'date'   
   ,CAST(@Val1 AS smalldatetime) AS  
        'smalldatetime'   
   ,CAST(@Val1 AS datetime) AS 'datetime'   
   ,CAST(@Val1 AS datetime2(7)) AS   
        'datetime2'  
   ,CAST(@Val1 AS datetimeoffset(7)) AS   
        'datetimeoffset'  
   ,CAST(@Val1 AS datetimeoffset(7)) AS  
        'datetimeoffset IS08601';

USE AT TIME ZONE

We can also use AT TIME ZONE to convert the input to the corresponding datetimeoffset value in the target time zone.

For example, we convert the value stored in our [DemoDataTable] column [Created_AT] in the Central European Standard Time.

SELECT created_at 
AT TIME ZONE 'Central European Standard Time'
        AS 'Central European Standard Time'
from DemoDataTable