CODEWITHSUNDEEP
oracle-databaseoracle11gtimezone
Pit
Pit

Reputation: 303

Issue with timezone conversion using 'AT TIME ZONE' clause

I have an Oracle 11g R2 table with two columns: timezone agnostic timestamp and document timezone as below:

EFF_START_DT                        TZ
31-DEC-08 09.00.00.000000000 PM     America/New_York
31-DEC-01 04.00.00.000000000 PM     Africa/Lome

I also have an application timezone defined in a separate table which I can query. I need to convert EFF_START_DT from application timezone to document timezone.

My test query works fine:

SELECT
EFF_START_DATE,
CAST(FROM_TZ(EFF_START_DATE, 'America/Los_Angeles') AT TIME ZONE TIMEZONE as date) AS EFF_DOC_START_DATE, 
TIMEZONE
FROM My_Tbl;

EFF_START_DT                    EFF_DOC_START_DT        TIMEZONE
31-DEC-08 09.00.00.000000000 PM 01-JAN-2009 00:00:00    America/New_York

I wrote Oracle row level function to do conversions dynamically:

CREATE OR REPLACE FUNCTION MN_FROM_TZ(P_TIMESTAMP IN TIMESTAMP, P_TO_DOC_TZ IN VARCHAR2)
RETURN VARCHAR2
AS
V_TIMESTAMP_TZ VARCHAR2(50);
V_APP_TZ VARCHAR2(255);
BEGIN
  SELECT OPTION_VALUE INTO V_APP_TZ FROM MN_DW_ETL_CONFIG_OPTIONS WHERE  OPTION_NAME='APP_TIMEZONE';
  SELECT cast(FROM_TZ(P_TIMESTAMP, V_APP_TZ) AT TIME ZONE P_TO_DOC_TZ as date) INTO V_TIMESTAMP_TZ FROM DUAL;

RETURN V_TIMESTAMP_TZ;
END;

It compiles successfully however when I try to run it:

SELECT EFF_START_DATE, MN_FROM_TZ(EFF_START_DATE, TIMEZONE) as EFF__DOC_START_DATE, TIMEZONE  FROM My_Tbl;

I get the following error:

ORA-00905: missing keyword
ORA-06512: at "ETLWINDM.MN_FROM_TZ", line 8
00905. 00000 -  "missing keyword"
*Cause:    
*Action:

The problem seems to be with P_TO_DOC_TZ parameter. The function works fine if I hard code this parameter as, for instance, 'America/New_York'. But I cannot do it as every row in my table will have different time zones. I guess there is something wrong with quotes though I can't find out what.

Upvotes: 1

Views: 894

Answers (1)

Noel
Noel

Reputation: 10525

Surprisingly, directly assigning the value to the variable instead of SELECT from DUAL, solves the issue.

SQLFiddle

CREATE OR REPLACE FUNCTION MN_FROM_TZ (P_TIMESTAMP   IN TIMESTAMP,
                                       P_TO_DOC_TZ   IN VARCHAR2)
   RETURN DATE
AS
   V_TIMESTAMP_TZ   DATE;
   V_APP_TZ         VARCHAR2 (255);
BEGIN
   SELECT app_tz INTO V_APP_TZ FROM t2;

   V_TIMESTAMP_TZ :=
      CAST (FROM_TZ (P_TIMESTAMP, V_APP_TZ) AT TIME ZONE P_TO_DOC_TZ AS DATE);

   RETURN V_TIMESTAMP_TZ;
END;
/

Also, note that the return type of the function and datatype of V_TIMESTAMP_TZ should be DATE, as you are casting timestamp value to DATE in your function.

Upvotes: 1

Related Questions