Friday, November 20, 2015

Storing datetime with time zone offset and retrieve with required time zone offset

Nothing new but posting as it is something important when working with multiple time zone offsets. If you have to store requests coming from all around the world with the time local to them, sysdatetimeoffset type has to be used with the column and when convert to a different time zone offset, switchoffset function has to be used. Here is an example;

USE tempdb;

-- creating table for holding date and time with time zone offset
CREATE TABLE dbo.TestTable
 Id int primary key,
 DateEntered datetimeoffset(0) not null index ix_TestTable clustered

-- inserting values
INSERT INTO dbo.TestTable
(1, '2015-11-17T02:10:33.430+10:30') -- receiving from Sydney
, (2, '2015-11-17T05:30:21.110+05:30') -- receiving from Sri Lanka
, (3, '2015-11-17T14:24:22.660+00:00') -- receiving from USA

-- Retrieving all dates for Sri Lanka time zone offset
SELECT Id, CONVERT(datetime, SWITCHOFFSET(DateEntered, '+05:30')) DateEntered
FROM dbo.TestTable;

No comments: