SWITCHOFFSET_UDF

Description

This custom UDF is added to return a datetimeoffset value that is changed from the stored time zone offset to a specified new time zone offset.

Custom UDF overloads

Parameters

1. source_timestamp: A TIMESTAMP_TZ that can be resolved to a datetimeoffset(n) value.

2. target_tz: A varchar that represents the time zone offset

UDF

CREATE OR REPLACE FUNCTION PUBLIC.SWITCHOFFSET_UDF(source_timestamp TIMESTAMP_TZ, target_tz varchar)
RETURNS TIMESTAMP_TZ
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS 
$$
WITH tz_values AS (
SELECT 
    RIGHT(source_timestamp::varchar, 5) as source_tz,
    
    REPLACE(source_tz::varchar, ':', '') as source_tz_clean,
    REPLACE(target_tz::varchar, ':', '') as target_tz_clean,
    
    target_tz_clean::integer - source_tz_clean::integer as offset,
    
    RIGHT(offset::varchar, 2) as tz_min,
    PUBLIC.OFFSET_FORMATTER(RTRIM(offset::varchar, tz_min)) as tz_hrs,
    
    
    TIMEADD( hours, tz_hrs::integer, source_timestamp ) as adj_hours,
    TIMEADD( minutes, (LEFT(tz_hrs, 1) || tz_min)::integer, adj_hours::timestamp_tz ) as new_timestamp
    
FROM DUAL)
SELECT 
    (LEFT(new_timestamp, 24) || ' ' || target_tz)::timestamp_tz
FROM tz_values
$$;

-- ==========================================================================
-- Description: The function OFFSET_FORMATTER(offset_hrs varchar) serves as
-- an auxiliar function to format the offter hours and its prefix operator.
-- ==========================================================================  
CREATE OR REPLACE FUNCTION PUBLIC.OFFSET_FORMATTER(offset_hrs varchar)
RETURNS varchar
LANGUAGE SQL
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"udf"}}'
AS
$$
CASE
   WHEN LEN(offset_hrs) = 0 THEN '+' || '0' || '0'
   WHEN LEN(offset_hrs) = 1 THEN '+' || '0' || offset_hrs
   WHEN LEN(offset_hrs) = 2 THEN
        CASE 
            WHEN LEFT(offset_hrs, 1) = '-' THEN '-' || '0' || RIGHT(offset_hrs, 1)
            ELSE '+' || offset_hrs
        END
    ELSE offset_hrs
END
$$;

SQL Server

Query

IN -> SqlServer_01.sql

SELECT 
  '1998-09-20 7:45:50.71345 +02:00' as fr_time,
  SWITCHOFFSET('1998-09-20 7:45:50.71345 +02:00', '-06:00') as cr_time;  

Result

SELECT 
  '1998-09-20 7:45:50.71345 +02:00' as fr_time,
  SWITCHOFFSET('1998-09-20 7:45:50.71345 +02:00', '-06:00') as cr_time;  

Snowflake

Query

OUT -> SqlServer_01.sql

SELECT
  '1998-09-20 7:45:50.71345 +02:00' as fr_time,
  PUBLIC.SWITCHOFFSET_UDF('1998-09-20 7:45:50.71345 +02:00', '-06:00') as cr_time;

Result

fr_time                         cr_time
------------------------------- ----------------------------------
1998-09-20 7:45:50.71345 +02:00 1998-09-19 23:45:50.7134500 -06:00

Related EWIs

1. SSC-EWI-0020: CUSTOM UDF INSERTED.