Re: checking for a legal date with PL/SQL
Date: 1998/01/07
Message-ID: <34b5b837.2726380_at_inet16>#1/1
On Wed, 7 Jan 1998 13:19:00 GMT, Michiel Betel <Michiel.Betel_at_cmg.nl> wrote:
>Does anyone have a generic PL/SQL function to check if a date is legal?
>my website generates a 8 letter varchar in the form DDMMYYYY which i
>want to covert to a date using TO_DATE() but does TO_DATE know about
>leapyears and such?
To_date is the function you want to use. It will only convert a date if it is a 'real' and valid date. For example:
SQL> select to_date( '29-FEB-1997', 'DD-MON-YYYY' ) from dual; select to_date( '29-FEB-1997', 'DD-MON-YYYY' ) from dual
*
ERROR at line 1:
ORA-01839: date not valid for month specified
SQL> c/97/98
1* select to_date( '29-FEB-1998', 'DD-MON-YYYY' ) from dual
SQL> /
select to_date( '29-FEB-1998', 'DD-MON-YYYY' ) from dual
*
ERROR at line 1:
ORA-01839: date not valid for month specified
SQL> c/1998/2000/
1* select to_date( '29-FEB-2000', 'DD-MON-YYYY' ) from dual
SQL> /
TO_DATE('
29-FEB-00 So, to_date will succeed if the date is valid, it will raise an error otherwise......
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Jan 07 1998 - 00:00:00 CET