0

I need to verify the format of input parameter of 'timestamp with time zone' type. It is passed as substitution variable in the script. The format is 'YYYY-MM-DD HH24:MI:SS TZH:TZM'. How should I verify that the passed value is given format. Which oracle function to use for this?

I cannot use to_timestamp_tz function as it will convert just char-exp into timestamp with tz. Here I want to validate the format of passed 'timestamp with time zone' variable.

PVJ
  • 79
  • 3
  • 9
  • Dates do not have any format. They are stored in an internal format which is Oracle proprietary. in a What you see is for display in human format. All you need is use a proper format model to ensure the explicit conversion happens. – Lalit Kumar B May 12 '15 at 11:52
  • You need a REGULAR EXPRESSION to validate a TIMESTAMP. This question is a possible duplicate of: http://stackoverflow.com/questions/1057716/regular-expression-to-validate-a-timestamp – UltraCommit May 12 '15 at 12:15
  • Moreover, possible duplicate of: http://stackoverflow.com/questions/14662597/how-to-check-regular-expression-for-timestamp-in-gwt – UltraCommit May 12 '15 at 12:20
  • It would be the worst idea to validate timestamps with regex. – Lalit Kumar B May 12 '15 at 12:32

1 Answers1

0

You can use TO_TIMESTAMP_TZ for ex:

SELECT TO_TIMESTAMP_TZ('1999-12-01 11:00:00 -8:00',
   'YYYY-MM-DD HH:MI:SS TZH:TZM') FROM DUAL;
Onur Cete
  • 263
  • 1
  • 2
  • 10
  • TO_TIMESTAMP_TZ converts char/varchar/varachar2 into timestamp with tz. I want to verify the format of timestamp with tz type – PVJ May 12 '15 at 11:45