Checking format of timestamp with time zone variable in oracle plsql

Question

I need to create a oracle plsql script which have input param v_date as Timestamp with time zone type. Also I need to verify whether the format of the v_date is as per mentioned format 'YYYY-MM-DD HH24:MI:SS TZH:TZM' or not . How should I achieve this?

I was thinking of achieveing this by changing input param v_date as String and then convert this string into Timestamp with time zone and if any error occured while converting then conclude that the format is wrong. But it seems TO_TIMESTAMP_TZ functions doesn't throw error when the date string is in different format w.r.t the specified format instead it converts it.

For e.g.

SELECT TO_TIMESTAMP_TZ('15-APR-15',
   'YYYY-MM-DD HH:MI:SS TZH:TZM') FROM DUAL;

Actual result : 14-APR-15 11.50.00.000000000 PM AMERICA/CHICAGO

Expected result : Some error as format is not as per 'YYYY-MM-DD HH:MI:SS TZH:TZM'

In short I want to know how to verify the format of Timestamp with time zone type.

There is a typo in the query , correct query : SELECT TO_TIMESTAMP_TZ('15-APR-15 ', 'YYYY-MM-DD HH:MI:SS TZH:TZM') FROM DUAL; — PVJ
How will you be converting the value to a string in order to pass it in? Where is the value coming from, and why do you think you can't just leave the parameter as Timestamp with time zone? — Boneist
My actual story says, accept input param v_date in the format "yyyy-MM-dd'T'HH:mm:ss.SSSZ" and then verify that the value passed in for v_date is in given "yyyy-MM-dd'T'HH:mm:ss.SSSZ" format. Till now whatever R&D I did on this, it seems "yyyy-MM-dd'T'HH:mm:ss.SSSZ" is not valid timestamp format in oracle. So now I am trying to implement the story with v_Date as string in 'YYYY-MM-DD HH:MI:SS TZH:TZM' and then use TO_TIMESTAMP_TZ() to verify the format of the parameter. — PVJ

Boneist Boneist · Accepted Answer · 2015-05-12T08:50:45

You could try adding the "fx" modifier to the format, which says to Oracle "expect this exact format":

SELECT TO_TIMESTAMP_TZ('15-APR-15 ', 'fxYYYY-MM-DD HH:MI:SS TZH:TZM') FROM DUAL;

SELECT TO_TIMESTAMP_TZ('15-APR-15 ', 'fxYYYY-MM-DD HH:MI:SS TZH:TZM') FROM DUAL
                       *
Error at line 1
ORA-01862: the numeric value does not match the length of the format item

Checking format of timestamp with time zone variable in oracle plsql

1 Answers