2

I am having SQL Server 2014 Express, I am given a SQL script which is having "AT TIME ZONE" in some queries. And as this functionality is provided in SQL Server 2016 so I can not run these queries/Script.

I am having Windows 7 so installing SQL Server 2016 is not an option because SQL Server 2016 do no support Windows 7.

What is the alternate of "AT TIME ZONE" queries for SQL Server 2014 or earlier versions of SQL Server?

Thanks,

Gulfam
  • 558
  • 6
  • 27
  • This might help https://stackoverflow.com/questions/16872007/date-time-conversion-from-timezone-to-timezone-in-sql-server – PSK May 09 '18 at 07:12
  • Muhammad you'd need to provide some sample queries where AT TIME ZONE is used in your queries. I say this because while AT TIME ZONE adds offset information where not present in time it also converts offset information when present. Both these need to be distinguised. – DhruvJoshi May 09 '18 at 07:30

1 Answers1

3

To begin with there is no direct alternatives to "AT TIME ZONE".It is the combination of features provided by two functions in earlier versions viz "SWITCHOFFSET" and "TODATETIMEOFFSET"

With the SWITCHOFFSET function, you can return an input DATETIMEOFFSET value in a requested offset term.It alters the existing DateTime value and the offset based on the second parameter(If provided DateTime is offset inspecific then it is considered as "0:00" offset).

With the TODATETIMEOFFSET,you can make an offset unaware DateTime to offset aware data. It only sets the offset to a value specified in the second parameter.

Now "AT TIME ZONE" this provides the good of both the above ones. As mentioned by Microsoft AT TIME ZONE,It Alters DateTime and Offset only if provided value is offset aware, else only offset is added.

SELECT
SWITCHOFFSET('20130212 14:00:00.0000000 -08:00', '-05:00') AS 
[SWITCHOFFSET_FROMOffsetAware],
SWITCHOFFSET('20130212 14:00:00.00', '-05:00') AS 
[SWITCHOFFSET_FROMOffsetUnAware],
TODATETIMEOFFSET('20130212 14:00:00.0000000 -08:00', '-05:00') AS 
[TODATETIMEOFFSET_FROMOffsetAware],
TODATETIMEOFFSET('20130212 14:00:00.00', '-05:00') AS 
[TODATETIMEOFFSET_FROMOffsetUnAware],
CAST('20130212 14:00:00.0000000 -08:00' as DATETIMEoffset)   
   AT TIME ZONE 'Easter Island Standard Time' as [TIMEZONE_FROMOffsetAware],
CAST('20130212 14:00:00' as DATETIME) 
   AT TIME ZONE 'Easter Island Standard Time' as [TIMEZONE_FROMOffsetUnAware];

See SqlFiddle

Now it should be seen that there is no direct alternative but, you could redo on case to case basis whether you should substitute "SWITCHOFFSET" or "TODATETIMEOFFSET" functions in place of "AT TIME ZONE" Hint.

To find an offset equivalent for time zone name use the following query(works with SQL 2014 or use fiddle)

select * from sys.time_zone_info
Samuel A C
  • 406
  • 3
  • 16