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, 3448🔥, 0💬
Popular Posts:
How to download Microsoft SQL Server 2005 Express Edition in SQL Server? Microsoft SQL Server 2005 E...
How To Calculate Age in Days, Hours and Minutes in SQL Server Transact-SQL? On many Web sites, news ...
How To Convert Character Strings into Numeric Values in SQL Server Transact-SQL? Sometimes you need ...
Where to find answers to frequently asked questions on PHP Connections and Query Execution for MySQL...
How To Update Multiple Rows with One UPDATE Statement in SQL Server? If the WHERE clause in an UPDAT...