What’s the local time?

SQL Server has a handy view called sys.time_zone_info, but as shown below it’s lacking the local time.

SELECT * FROM sys.time_zone_info;


 
The following is one way to workout the local time:

SELECT *,
DATEADD(MINUTE,
CAST(
SUBSTRING ( current_utc_offset ,1 , 1 )  
+ CAST(
(CAST(SUBSTRING(current_utc_offset ,2 , 2 ) AS int) * 60) 
+ CAST(SUBSTRING(current_utc_offset,5,2) AS int) 
AS varchar(3))
AS int)
,GETUTCDATE()) AS local_time
FROM sys.time_zone_info;


Or just a specific zone:

SELECT *,
DATEADD(MINUTE,
CAST(
SUBSTRING ( current_utc_offset ,1 , 1 )  
+ CAST(
(CAST(SUBSTRING(current_utc_offset ,2 , 2 ) AS int) * 60) 
+ CAST(SUBSTRING(current_utc_offset,5,2) AS int) 
AS varchar(3))
AS int)
,GETUTCDATE()) AS local_time
FROM sys.time_zone_info
WHERE [name] = 'New Zealand Standard Time';


Hopefully at some stage Microsoft will look to include the local info by default so this kind of work around is not required, especially as Azure is heavily invested in UTC.

Leave a Reply