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, 3269🔥, 0💬
Popular Posts:
What Is "mysqld" in MySQL? "mysqld" is MySQL server daemon program which runs quietly in background ...
How To Change the Name of a Database User in SQL Server? If you want to change the name of an existi...
How To Query Tables and Loop through the Returning Rows in MySQL? The best way to query tables and l...
How To Replace Given Values with NULL using NULLIF() in SQL Server Transact-SQL? Sometime you want t...
What Is Oracle in Oracle? Oracle is a company. Oracle is also a database server, which manages data ...