Verifying dates in SQL

Philip Greenspun's Homepage : Philip Greenspun's Homepage Discussion Forums : 6916 : One Thread

Hey, philg mentioned something in lecture one day about a nifty way
to check dates for validity as well as checking that start_time <
end_time. It had something to do with putting a catch around a sql
query to the dual (dummy) table. Does anyone remember how this was
written?

-- Matthew Lee, October 3, 1999

Answers

In SQLPLUS, you should try the following SQL statements. Once you see the results, you should have a better idea of what Philip suggested in lecture.

select sysdate from dual;
select to_date('1999-10-02','YYYY-MM-DD') from dual;
select to_date('1999-14-02','YYYY-MM-DD') from dual;
select to_date('1999-11-01','YYYY-MM-DD') - sysdate from dual;
Now that you how smart Oracle is...at least smart enough to not give back an invalid date or convert a string which does not match the format you expected. So, as you already stated, just wrap some SQL with a catch.

-- Brian Stein, October 3, 1999

There's a better way: ns_buildsqldate (check the docs at www.aolserver.com if I'm wrong).
It'll validate your date and give you can insert into a date field....

-- Ben Walter, October 11, 1999