FROM_TZ Function - Oracle to SQL Server Migration

In Oracle the FROM_TZ function allows you set the specified time zone for a timestamp value that does not have the timezone.

In SQL Server you can use AT TIME ZONE operator, but note that the supported timezone values can be different (see below).

Oracle:

  -- Set UTC time zone
  SELECT FROM_TZ(TIMESTAMP '2023-07-30 19:22:11', 'UTC') FROM dual;
  # 30-JUL-23 07.22.11.000000000 PM UTC
 
  -- Set Eastern time zone
  SELECT FROM_TZ(TIMESTAMP '2023-07-30 19:22:11', 'EST') FROM dual;
  # 30-JUL-23 07.22.11.000000000 PM EST

SQL Server:

  -- Set UTC time zone
  SELECT CAST('2023-07-30 19:22:11' AS DATETIME) AT TIME ZONE 'UTC';
  #  2023-07-30 19:22:11.000 +00:00
 
  -- Set Eastern time zone
  SELECT CAST('2023-07-30 19:22:11' AS DATETIME) AT TIME ZONE 'Eastern Standard Time';
  # 2023-07-30 19:22:11.000 -04:00

Note that in the example above you had to change 'EST' timezone in Oracle to SQL Server 'Eastern Standard Time' in SQL Server, but it was not required for 'UTC' value. Check sys.time_zone_info view in SQL Server to list available time zone values.

Note that AT TIME ZONE operator is also available in Oracle but unlike FROM_TZ function it changes the value from your current local timezone (UTC +2 in my case):

Oracle:

  -- Set UTC time zone
  SELECT TIMESTAMP '2023-07-30 19:22:11' AT TIME ZONE 'UTC' FROM dual;
  # 30-JUL-23 05.22.11.000000000 PM UTC
 
  -- Set Eastern time zone
  SELECT TIMESTAMP '2023-07-30 19:22:11' AT TIME ZONE 'EST' FROM dual;
  # 30-JUL-23 12.22.11.000000000 PM EST

You can use two subsequent AT TIME ZONE operators to convert a time stamp value from one time zone into another in Oracle and SQL Server, but note that the result is different due to conversions in Oracle:

Oracle:

  -- Convert from UTC time zone to EST 
  SELECT (TIMESTAMP '2023-07-30 19:22:11' AT TIME ZONE 'UTC') AT TIME ZONE 'EST' FROM dual;
  # 30-JUL-23 12.22.11.000000000 PM EST

SQL Server:

  -- Convert from UTC time zone to EST
  SELECT CAST('2023-07-30 19:22:11' AS DATETIME) AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time'; 
  # 2023-07-30 15:22:11.000 -04:00

For more information, see Oracle to SQL Server Migration.