Often while working with the people in different time zone, we stuck and stop to calculate what the current time is in their time zone and what the current time it is in my time zone. There are a lot of fine utilities available on the internet to use. I thought of why not a dashboard kind of report which can tell me what the current time is in different time zone. So I fired an SSMS and write down this script which can tell me what is the current time in different time zone.
I used the GETUTCDATE() function which has been introduced with SQL Server 2005 onwards; This function returns the UTC date and time. UTC Time are also referred as Co-ordinated Universal Time or Greenwich Mean Time. This function internally calculate the local date and time and time zone of the SQL Server box and convert that to UTC date and time.
For the different time zone(like EST, PST, AST etc.), I just had to know that how far they are from the UTC. Like ATS (Atlantic Time) is 4 hours before UTC Time so I have to calculate UTC Date/Time and deduct 4 hours from it to get the current ATS Time.
--UTC Time
SELECT 'UTC Time' as 'TimeZone','UTC' as 'TimeAbb' ,CONVERT(varchar(20),GETUTCDATE(),113) as 'Current Time'
UNION ALL
--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'
UNION ALL
-- Atlantic Time = UTC-4
SELECT 'Atlantic Time' as 'TimeZone','ATS' as 'TimeAbb',CONVERT(varchar(20),DATEADD(hh,-4,GETUTCDATE()),113) as 'Current Time'
UNION ALL
-- Eastern Time = UTC-5
SELECT 'Eastern Time' as 'TimeZone','ETS' as 'TimeAbb',CONVERT(varchar(20),DATEADD(hh,-5,GETUTCDATE()),113) as 'Current Time'
UNION ALL
-- Mountain Time = UTC-6
SELECT 'Mountain Time' as 'TimeZone','MTS' as 'TimeAbb',CONVERT(varchar(20),DATEADD(hh,-6,GETUTCDATE()),113) as 'Current Time'
UNION ALL
-- Pacific Time = UTC-8
SELECT 'Pacific Time' as 'TimeZone','PST' as 'TimeAbb',CONVERT(varchar(20),DATEADD(hh,-8,GETUTCDATE()),113) as 'Current Time'
UNION ALL
-- Alaska Time = UTC-9
SELECT 'Alaska Time' as 'TimeZone','AKST' as 'TimeAbb',CONVERT(varchar(20),DATEADD(hh,-9,GETUTCDATE()),113) as 'Current Time'
UNION ALL
-- Hawai Time = UTC-10
SELECT 'Hawai Time' as 'TimeZone','HST' as 'TimeAbb',CONVERT(varchar(20),DATEADD(hh,-10,GETUTCDATE()),113) as 'Current Time'
I put this script in a SSRS report and this is now seating at my desktop and every time I need to see the current date time of different time zone; I am just one click away.
So I can enjoy my knowledge and have my own little world clock…:)
