Ask the Expert

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 column is a valid date including leap years? Example: A_Date varchar2(8)
A_Date    Valid
--------  -----
20030131  Yes
20030631  No

    Requires Free Membership to View

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;


This was first published in July 2003

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: