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, 3745🔥, 0💬
Popular Posts:
How to set the current database in SQL Server? Once you are connected to the SQL Server, you should ...
Where to find answers to frequently asked questions on PHP Connections and Query Execution for MySQL...
How To Divide Query Output into Multiple Groups with the GROUP BY Clause in SQL Server? Sometimes, y...
How To End a Stored Procedure Properly in SQL Server Transact-SQL? Where the end of the "CREATE PROC...
How To Create a Table Index in Oracle? If you have a table with a lots of rows, and you know that on...