AT TIME ZONE

New time functions in SQL 2016

Converts an inputdate to the corresponding datetimeoffset value in the target time zone. If inputdate is provided without offset information, the function applies the offset of the time zone assuming that inputdate value is provided in the target time zone. If inputdate is provided as a datetimeoffset value, than AT TIME ZONE clause converts it into the target time zone using time zone conversion rules.

Example

SET NOCOUNT ON
declare @d datetimeoffset(3);
declare @n varchar(100);
set @d = SYSUTCDATETIME()

print ‘DATE & TIME @ UTC: ‘ + convert(VARCHAR, @d, 0);

— KEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones
— select * from sys.time_zone_info

select @n=name from sys.time_zone_info
where name like ‘%Central europe St%’

set @d = SYSDATETIMEOFFSET() AT TIME ZONE @n
print ‘DATE & TIME @ Madrid (‘ + @n + ‘): ‘ + convert(VARCHAR, @d, 0);

Deja un comentario