I need to check that my string is in a format of time
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;
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.
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.