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;

view plainprint?

1. USE tempdb;  
2. GO  
3.   
4. -- creating table for holding date and time with time zone offset  
5. CREATE TABLE dbo.TestTable  
6. (  
7.  Id int primary key,  
8.  DateEntered datetimeoffset(0) not null index ix_TestTable clustered  
9. );  
10.   
11. -- inserting values  
12. INSERT INTO dbo.TestTable  
13. VALUES  
14. (1, '2015-11-17T02:10:33.430+10:30') -- receiving from Sydney  
15. , (2, '2015-11-17T05:30:21.110+05:30') -- receiving from Sri Lanka  
16. , (3, '2015-11-17T14:24:22.660+00:00') -- receiving from USA  
17.   
18. -- Retrieving all dates for Sri Lanka time zone offset  
19. SELECT Id, CONVERT(datetime, SWITCHOFFSET(DateEntered, '+05:30')) DateEntered  
20. FROM dbo.TestTable;  

USE tempdb;
GO

-- 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
VALUES
(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;