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, 3287🔥, 0💬
Popular Posts:
What Happens to Your Transactions When ERROR 1213 Occurred in MySQL? If your transaction receives th...
Where to find MySQL database server tutorials? Here is a collection of tutorials, tips and FAQs for ...
How To Convert Numeric Values to Character Strings in MySQL? You can convert numeric values to chara...
How To List All User Names in a Database in SQL Server? If you want to see a list of all user names ...
How To Connect the Oracle Server as SYSDBA in Oracle? This is Step 4. The best way to connect to the...