I need to determine if a VARCHAR2 column contains a valid date. Is there a function to test if the value in the
column is a valid date including leap years? Example: A_Date varchar2(8)
A_Date Valid -------- ----- 20030131 Yes 20030631 No
The function TO_DATE can determine if a string represents a valid date. The problem with TO_DATE is that it won't tell you politely when the string is not a valid date: it will raise an error instead. How can you keep it from raising an error? EXCEPTION:
CREATE OR REPLACE FUNCTION to_dt ( in_dt_text IN VARCHAR2, in_fmt_text IN VARCHAR2 DEFAULT 'YYYYMMDD' ) RETURN DATE IS BEGIN RETURN TO_DATE ( in_dt_text, in_fmt_text ); EXCEPTION WHEN OTHERS THEN RETURN NULL; END to_dt; / SELECT a_date, NVL2 ( to_dt (a_date), 'Yes', 'No' ) AS valid FROM a_table;The final output wanted is a string, "Yes" or "No". Why, then, did I choose to make the to_dt function return a DATE instead of a VARCHAR2? In the long run, it saves time to write reusable functions, code that answers the question you have today, but can also be used to answer the question you may get next year. Next year, I may be asked "Does this VARCHAR2 column represent a date, and, if so, is that date in the future?". Reusability is also the reason I made the format mask an argument to to_dt: next year I may have to deal with dates in 'MM/DD/YYYY' format.
If I really hated having that messy NVL2 call in my code, I would hide it (or rather its equivalent, since PL/SQL doesn't support NVL2 yet) in another function:
CREATE OR REPLACE FUNCTION to_dt_yes_no ( in_dt_text IN VARCHAR2, in_fmt_text IN VARCHAR2 DEFAULT 'YYYYMMDD' ) RETURN VARCHAR2 IS BEGIN IF to_dt ( in_dt_text, in_fmt_text ) IS NULL THEN RETURN 'No'; ELSE RETURN 'Yes'; END IF; END to_dt_yes_no; / SELECT a_date, to_dt_yes_no (a_date) AS valid FROM a_table;
Dig deeper on Using Oracle PL-SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.