SQL Server 2008 – All Time Zones – UTC – IST – Atlantic Time – Eastern Time – Mountain Time – Pacific Time – Alaska Time – Hawai Time

Often we encounter problem in converting and storing the correct date time in databases. The major problem comes when we have a web site for specific country or open to all the world and our Server location is somewhere else and storing the data according to the user or client location becomes very difficult.

Below are some most commonly used Time Zones which can be made use of so that it can be kept in the logic while storing the data correctly according to the time zone.

–UTC Time

SELECT ‘UTC Time’ as ‘TimeZone’,’UTC’ as ‘TimeAbb’ ,CONVERT(varchar(20),GETUTCDATE(),113) as ‘Current Time’


–IST Time i.e. India Time=UTC+5:30

SELECT ‘India Time’ as ‘TimeZone’,’IST’ as ‘TimeAbb’,CONVERT(varchar(20),DATEADD(MI,30,DATEADD(hh,5,GETUTCDATE())),113) as ‘Current Time’


— Atlantic Time = UTC-4

SELECT ‘Atlantic Time’ as ‘TimeZone’,’ATS’ as ‘TimeAbb’,CONVERT(varchar(20),DATEADD(hh,-4,GETUTCDATE()),113) as ‘Current Time’


— Eastern Time = UTC-5

SELECT ‘Eastern Time’ as ‘TimeZone’,’ETS’ as ‘TimeAbb’,CONVERT(varchar(20),DATEADD(hh,-5,GETUTCDATE()),113) as ‘Current Time’


— Mountain Time = UTC-6

SELECT ‘Mountain Time’ as ‘TimeZone’,’MTS’ as ‘TimeAbb’,CONVERT(varchar(20),DATEADD(hh,-6,GETUTCDATE()),113) as ‘Current Time’


— Pacific Time = UTC-8

SELECT ‘Pacific Time’ as ‘TimeZone’,’PST’ as ‘TimeAbb’,CONVERT(varchar(20),DATEADD(hh,-8,GETUTCDATE()),113) as ‘Current Time’


— Alaska Time = UTC-9

SELECT ‘Alaska Time’ as ‘TimeZone’,’AKST’ as ‘TimeAbb’,CONVERT(varchar(20),DATEADD(hh,-9,GETUTCDATE()),113) as ‘Current Time’


— Hawai Time = UTC-10

SELECT ‘Hawai Time’ as ‘TimeZone’,’HST’ as ‘TimeAbb’,CONVERT(varchar(20),DATEADD(hh,-10,GETUTCDATE()),113) as ‘Current Time’


Display all time Zones

Reference : Dilip Kumar Jena (https://sqlexplore.wordpress.com)