This content has been marked as final. Show 9 replies
There are multiple ways you could accomplish that.
1. You could use a TO_DATE() function and capture the error as shown below:
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.
2. Let the error propagate back up to the client upon insert and handle it there.
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 ?
Yes: you can call TO_DATE.
For example, if you want to do the INSERT whether or not string_var contains a valid date:
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;
Edited by: Frank Kulash on Aug 14, 2009 7:23 AM
thanks all for the feedback so far, maybe I should give a bit more information about my situation. I have more then 1000 rows in a table, one column is a possible date, but the data type is VARCHAR2 for a good reason. what I want to make is one! sql statement, that returns me every row where the column does not has a correct date. the format could be different, for example its 01.01.2009 or 01.01.09, what it makes a bit more complicate. 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.
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.
You can write something that loops through any number of formats, in order, and returns one value as soon as it finds a format that works with the given string, or returns something else if all the formats fail. You woud have to list all the possible formats you want it to test.
What are you going to do with '01/02/2009'? It matches 'dd/mm/yyyy' and 'mm/dd/yyyy' formats equally well. Is '01/02/2009' January 2 or February 1?