Q

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

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

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close