Determining if VARCHAR2 column contains valid date
I need to determine if a VARCHAR2 column contains a valid date. Is there a function to test if the value in the...
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
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;
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;