Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - Formatting Time Zone in +/-hh:mm Format

By: FYIcenter.com

(Continued from previous topic...)

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

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.

(Continued on next topic...)

1. How To Add or Remove Days on Date and Time Values?
2. Can Date and Time Values Be Converted into Integers?
3. Can Integers Be Converted into Date and Time Values?
4. Are DATETIME and NUMERIC Values Convertible?
5. Can a DATETIME Value Be Subtracted from Another DATETIME Value?
6. What Are the Date and Time Functions Supported by SQL Server 2005?
7. How To Increment or Decrement Parts of DATETIME Values?
8. How To Use DATEADD() Function?
9. How To Calculate DATETIME Value Differences Using the DATEDIFF() Function?
10. How To Calculate Age in Days, Hours and Minutes?
11. How To Get Month and Weekday Names from DATETIME Values?
12. How To Get Parts of DATETIME Values as Integers?
13. How To Get Year, Month and Day Out of DATETIME Values?
14. What Is the Difference Between GETDATE() and GETUTCDATE()?
15. How To Format Time Zone in +/-hh:mm Format?
16. How To Format DATETIME Values to Strings with the CONVERT() Function?
17. How To Truncate DATETIME Values to Dates without Time?
18. How To Set Different Parts of a DATETIME Value?