0

Is it possible to get the name of the current time zone, such as "Eastern Standard Time" or "EST" in SQL code on SQL Server 2008 R2 ? I know I can determine numeric local time offset by calculating difference between getdate() and getutcdate(), but that's not what I need. I simply need TZ name from the underlying operating system.

Cozzamara
  • 1,318
  • 1
  • 14
  • 22
  • You can do this with SQL CLR, but why do you need it in SQL? It would be much easier to do it in your application code. – Matt Johnson-Pint Jan 21 '13 at 22:54
  • @MattJohnson of course, there are plenty of ways to get time zone in other languages, but I need this specifically in SQL code in stored procedure because of specifics of my application – Cozzamara Jan 21 '13 at 23:03
  • Then put the list in a table. – Aaron Bertrand Jan 21 '13 at 23:09
  • See [this question](http://stackoverflow.com/questions/614600/access-timezoneinfo-from-sql-2005-server) for how to use `TimeZoneInfo` from SQL Server. Once you get there, just hit `TimeZoneInfo.Local.Id` or `TimeZoneInfo.Local.DisplayName` depending on what you want. – Matt Johnson-Pint Jan 21 '13 at 23:11

1 Answers1

4

Without any defined table, if you just want to read the time zone information from the System Information, then it can be read from the system registry.

declare @TZName varchar(50)

exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',
'TimeZoneKeyName',@TZName OUT

select @TZName

Raj

Raj
  • 10,653
  • 2
  • 45
  • 52
  • I've seen that article - it doesn't answer my question or helps in any way. I don't need time zone lists, and I don't care about zone offset value. Single offset value (in relation to UTC) corresponds to multiple different time zones, which are different at different times of the year, so having TZ lists doesn't help. – Cozzamara Jan 22 '13 at 04:35
  • Registry method works, thanks - that's all I needed really. Please remove mentioning of MSDN blog and tz lists from your answer, so not to confuse future readers. – Cozzamara Jan 22 '13 at 04:55
  • Removed. Registry method might need elevated permissions, though. Could you please mark the answer as accepted? – Raj Jan 22 '13 at 05:02
  • 1
    Be aware that this registry value wasn't introduced until Windows Vista and Windows Server 2008. If you are supporting Windows 2003 servers, you can't get it this way. Also, read [this related kb article](http://support.microsoft.com/kb/2562433). Another point, the *key name* is the *id* - which is not always the right value to display to a user. [See the list here](http://support.microsoft.com/kb/973627). For example, I live in Arizona, and while the display name is `Arizona`, the id is `US Mountain Standard Time`. For places that use daylight savings, the id won't show that either. – Matt Johnson-Pint Jan 22 '13 at 15:55