How To Format Time Zone in +/-hh:mm Format in SQL Server Transact-SQL?

From the previous tutorial, you know how to calculate the time zone value by using GETDATE() and GETUTCDATE() functions. But how can you format that time zone value in a nice looking format like +05:45 or -04:00?

Unfortunately, SQL Server 2005 offers no built-in functions to convert a date and time value in your own specific format. You have to do this by yourself as shown in the tutorial exercise:

-- time_zone_formatter
DECLARE @local_time DATETIME;
DECLARE @gmt_time DATETIME;
SET @local_time = 'May 19 2007 10:06PM';
SET @gmt_time = 'May 19 2007  4:21PM';
SELECT 'Server local time: '
   + CONVERT(VARCHAR(40),@local_time);
SELECT 'Server GMT time: '
   + CONVERT(VARCHAR(40),@gmt_time);
SELECT 'Server time zone: '
   + CASE 
        WHEN DATEDIFF(minute,@gmt_time,@local_time)>= 0 THEN
           '+'
        ELSE
           '-'
        END
   + RIGHT(CONVERT(VARCHAR(40),
      100+ABS(DATEDIFF(minute,@gmt_time,@local_time)/60)),
      2)
   + ':'
   + RIGHT(CONVERT(VARCHAR(40),
      100+ABS(DATEDIFF(minute,@gmt_time,@local_time)%60)),
      2);
GO
Server local time: Jun  2 2007 10:06PM
Server GMT time: Jun  2 2007  4:21PM
Server time zone: +05:45

By the way, if you are wondering which part of the world has a time zone of +05:45. It is in the area of Kathmandu, capital city of Nepal.

 

⇒Date/Time Operations and Functions in SQL Server Transact-SQL

⇒⇒SQL Server Transact-SQL Tutorials