SQL DATETIMEOFFSET Data Type

The DATETIMEOFFSET data type is a date and time with time zone awareness.

DATETIMEOFFSET supports dates from 0001-01-01 through 9999-12-31.

The default value is 1900-01-01 00:00:00 00:00.

The time is based on 24-hour UTC clock. UTC = Universal Time Coordinate or Greenwich Mean Time.


Example

#

A table with a DATETIMEOFFSET column.

CREATE TABLE DemoTable  
( 
  Id INT IDENTITY, 
  Sender VARCHAR(50) NOT NULL,
  Subject VARCHAR(50) NOT NULL,
  DateTimeSent DATETIMEOFFSET
);
GO  

INSERT INTO DemoTable VALUES ('Harold Smith', 'After Sales', '2022-02-15 9:55:12 +8:00');
INSERT INTO DemoTable VALUES ('Robert Wang', 'Technical Issues', '2022/11/08 8AM -8:00');
INSERT INTO DemoTable VALUES ('Janice Lopez', 'Feedback', '2022-05-14 13:12 -10:00');
INSERT INTO DemoTable VALUES ('Kelly Wilson', 'After Sales', '2022-9-2 5PM +12:00');
INSERT INTO DemoTable VALUES ('Grace Taylor', 'Feedback', NULL);
GO  

SELECT * FROM DemoTable;
GO

DROP TABLE DemoTable;
GO
Results:  5 records
Id Sender Subject DateTimeSent
1 Harold Smith Lays Potato Chips 2022-02-15 09:55:12.0000000 +08:00
2 Robert Wang Quaker Oats 2021-11-08 08:00:00.0000000 -08:00
3 Janice Lopez Oreo Cookies 2021-05-14 13:12:00.0000000 -10:00
4 Kelly Wilson Fritos 2021-09-02 00:00:00.0000000 +12:00
5 Grace Taylor Cheetos NULL

Notice how SQL Server accepts different input date and time formats.

These formats are converted to the internal format which is 'YYYY-MM-DD HH:MM:SS.NNNNNNN HH:MM'.

More Examples

DATETIME with CAST DATETIMEOFFSET VALUE

ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
CUSTOMER
Id
FirstName
LastName
City
Country
Phone
Problem: List all orders. Display the order dates and time with time zone awareness each row.
SELECT OrderNumber, FirstName, LastName, 
       CAST(OrderDate AS DATETIMEOFFSET) AS [DateTimeOffset],
	   TotalAmount
  FROM [Order] O
  JOIN Customer C ON C.Id = O.CustomerId
Result:  830 records
OrderNumber FirstName LastName DateTimeOffset TotalAmount
542378 Paul Henriot 2012-07-04 00:00:00.000000 +00:00 440.00
542379 Karin Josephs 2012-07-05 00:00:00.000000 +00:00 1863.40
542380 Mario Pontes 2012-07-08 00:00:00.000000 +00:00 1813.00
542381 Mary Saveley 2012-07-08 00:00:00.000000 +00:00 670.80
542382 Pascale Cartrain 2012-07-09 00:00:00.000000 +00:00 3730.00

DATETIMEOFFSET with OTHER DATE AND TIME TYPES

Problem: Display date values as different date and time types in SQL Server.
CREATE TABLE DemoTable  
( 
  MyDate DATE,
  MyTime TIME,
  MySmallDateTime SMALLDATETIME,
  MyDateTime DATETIME,
  MyDateTime2 DATETIME2,
  MyDateTimeOffset DATETIMEOFFSET
);
GO  

INSERT INTO DemoTable VALUES ('2021-10-22 13:54:19:55', '2021-10-22 13:54:19:55',
                              '2021-10-22 13:54:19:55', '2021-10-22 13:54:19:55',
                              '2021-10-22 13:54:19:55', '2021-10-22 13:54:19:55');  
GO  

SELECT * FROM DemoTable;
GO

DROP TABLE DemoTable;
GO
Result:  1 record
MyDate MyTime MySmallDateTime MyDateTime MyDateTime2 MyDateTimeOffset
2021-10-22 13:54:19.0550000 2021-10-22 13:54:00 2021-10-22 13:54:19.057 2021-10-22 13:54:19.0550000 2021-10-22 13:54:19.0550000 +00:00

You may also like



Guides


vsn 3.1