If the date input is not in proper format the it will give error. You can catch it in an exception block and handle it.
dt_variable := TO_DATE(<INPUT_DATE>,<DATE_FORMAT>)
However the problem with this is that you must now every combination of how a date format can be incorrect (bad month, bad year, bad day of month, etc) so it may not be effective.
SQL > DECLARE 2 bad_month_format EXCEPTION; 3 PRAGMA EXCEPTION_INIT(bad_month_format,-01843); 4 sample_date DATE; 5 BEGIN 6 sample_date := TO_DATE('99/01/2009','MM/DD/YYYY'); 7 EXCEPTION 8 WHEN bad_month_format THEN 9 DBMS_OUTPUT.PUT_LINE('Caught bad date format!'); 10 END; 11 / Caught bad date format! PL/SQL procedure successfully completed.
Ikrischer wrote:Hi, Ilja,
I got a string in a PL/SQL Routine and need to know if it is a proper date format or not. Is there any function or regular expression for it, so I can check the string before I Insert it into a column with a date type ?
This is one of the few times I would use "EXCEPTION WHEN OTHERS THEN", because there are a lot of different errors could be raised by TO_DATE. Make sure the only statement in that BEGIN block is the assignment with TO_DATE.
BEGIN ... BEGIN date_var := TO_DATE (string_var, 'DD-Mon-YYYY HH24:MI:SS;); -- or whatever format EXCEPTION WHEN OTHERS THEN dbms_output.put_line ('Can''t insert bad date: ' || string_var); END; INSERT INTO my_table (..., dt_col) VALUES (..., date_var); ... END;
Ikrischer wrote:No, there's nothing I know about that includes more than one format.
... I hoped there is a string function already like IS_DATE so I could use it in my qeury. but it looks like I have to write my own function and return 0 for a correct date and 0 if not.