Collections:
Formatting Time Zone in +/-hh:mm Format in SQL Server
How To Format Time Zone in +/-hh:mm Format in SQL Server Transact-SQL?
✍: FYIcenter.com
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.
⇒ CONVERT() - Formatting DATETIME Values to Strings in SQL Server
⇐ Difference Between GETDATE() and GETUTCDATE() in SQL Server
⇑ Date/Time Operations and Functions in SQL Server Transact-SQL
2017-02-08, 3296🔥, 0💬
Popular Posts:
What Is ISAM in MySQL? ISAM (Indexed Sequential Access Method) was developed by IBM to store and ret...
How to download and install Microsoft SQL Server Management Studio Express in SQL Server? Microsoft ...
How To Set Up SQL*Plus Output Format in Oracle? If you want to practice SQL statements with SQL*Plus...
How To Convert a Unicode Strings to Non-Unicode Strings in SQL Server Transact-SQL? Since Unicode ch...
What Is ISAM in MySQL? ISAM (Indexed Sequential Access Method) was developed by IBM to store and ret...