AT TIME ZONE

Description

Converts an inputdate to the corresponding datetimeoffset value in the target time zone. (AT TIME ZONE in Transact-SQL).

Sample Source Pattern

Syntax

SQL Server

inputdate AT TIME ZONE timezone

Snowflake SQL

Snowflake SQL Documentation

CONVERT_TIMEZONE( <source_tz> , <target_tz> , <source_timestamp_ntz> )

CONVERT_TIMEZONE( <target_tz> , <source_timestamp> )

Examples

SqlServer

SELECT CAST('2022-11-24 11:00:45.2000000 +00:00' as datetimeoffset) at time zone 'Alaskan Standard Time';

Result:

                          DATE|
------------------------------+
2022-11-24 02:00:45.200 -09:00|

SnowFlake

SELECT
CONVERT_TIMEZONE('America/Anchorage', CAST('2022-11-24 11:00:45.2000000 +00:00' as TIMESTAMP_TZ(7)));

Result:

                          DATE|
------------------------------+
2022-11-24 02:00:45.200 -09:00|

SQL Server

Code:

SELECT current_timestamp at time zone 'Central America Standard Time';

Result:

                          DATE|
------------------------------+
2022-10-10 10:55:50.090 -06:00|

Snowflake SQL

Code:

SELECT
CONVERT_TIMEZONE('America/Costa_Rica', current_timestamp /*** MSC-INFORMATION - MSCINF0037 - CURRENT_TIMESTAMP in At Time Zone statement may have a different behavior in certain cases ***/);

Result:

                          DATE|
------------------------------+
2022-10-10 10:55:50.090 -06:00|

Known Issues

1. Snowflake does not support all the time zones that SQL Server does. You can check the supported time zones at this link.

SQL Server

SELECT current_timestamp at time zone 'Turks And Caicos Standard Time';

Result:

                          DATE|
------------------------------+
2022-12-14 20:04:18.317 -05:00| 

Snowflake

-- ** MSC-ERROR - MSCEWI4063 - TIME ZONE NOT SUPPORTED IN SNOWFLAKE **
--SELECT
--current_timestamp at time zone 'Turks And Caicos Standard Time'

Related EWIs

• MSCEWI4063: Time zone not supported in Snowflake.

• MSCINF0037: CURRENT_TIMESTAMP in At Time Zone statement may have a different behavior in certain cases.