How to check time format in Oracle-CodePudding

I need to check that my string is in a format of time HH24:MI:SS.

I made a function

CREATE OR REPLACE FUNCTION check_time(myStr VARCHAR2) RETURN INT IS
   p_temp DATE;
BEGIN
     p_temp := TO_DATE(myStr, 'HH24:MI:SS');    
   RETURN 1;
 EXCEPTION WHEN others THEN RETURN 0;
END;

But the TO_DATE() not failing when getting number between 0 and 23; I need the string to be in the exact format of HH24:MI:SS with the colon.

I looking for a solution that uses the Oracle Date/Time formats.

CodePudding user response：

If you want to prevent Oracle from apply lax rules to the conversion then you can add the 'FX' format modifier:

p_temp := TO_DATE(myStr, 'FXHH24:MI:SS');

On recent versions of Oracle you don't need your own function, you can use validate_conversion(), which will give you the same 0/1 result:

validate_conversion('<your_string>' as date, 'FXHH24:MI:SS')

fiddle with some sample valid and invalid values.