0

I need to convert a datetime stored as UTC to a specific timezone, stored in another column.

For example, given this table:

Date             | TimeZone
-----------------+---------------
2017-05-15 11:17 | Europe/Madrid
2017-01-01 11:17 | Europe/Madrid
2017-05-15 11:17 | US/Pacific
2017-01-01 11:17 | US/Pacific

I should get this result:

Date
----------------
2017-05-15 13:17
2017-01-01 12:17
2017-05-15 04:17
2017-01-01 03:17

Please note I'm using SQL Server 2012, so I can't use AT TIME ZONE. Is there any way to achieve this without post-processing the data?

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
César García Tapia
  • 3,326
  • 4
  • 27
  • 51
  • 4
    Possible duplicate of [How does one use TimeZoneInfo in a SQLCLR assembly in SQL Server 2012](https://stackoverflow.com/questions/17581510/how-does-one-use-timezoneinfo-in-a-sqlclr-assembly-in-sql-server-2012). The answer contains a link to a project that will add TZ support to SQL Server 2008 R2+. – Jeroen Mostert Jun 01 '17 at 11:44
  • 1
    Possible duplicate of [Use SQL 2012 Server to convert UTC time to base local time](https://stackoverflow.com/questions/12452430/use-sql-2012-server-to-convert-utc-time-to-base-local-time) – Rachel Ambler Jun 01 '17 at 12:01

1 Answers1

1

Unfortunately it is not that easy. You'd need information about different time zones and rules of daylight-saving switches etc. I'm using sqltz library for all my timezone-related needs. 

  SELECT LocalDate = Tzdb.UtcToLocal(UTCDate, TimeZone, 1, 1)
  FROM Table

sqltz uses IANA time zone database, Microsoft uses different standard so names of time zones are different. Europe/Madrid and US/Pacific are in IANA database

Alsin
  • 1,514
  • 1
  • 14
  • 18