Create a function that determines if the date is valid: create or replace function isdate(p_inDate date, p_format varchar2) return number as v_dummy date; begin select to_date(p_inDate,p_format) into v_dummy from dual; return 0; exception when others then return 1; end isdate;
Then use a case statement in your select statements (don't know the specific Oracle format) that uses the above function. Then you should be able to filter on just results from the function. Ray Thompson Tau Beta Pi (www.tbp.org) The Engineering Honor Society 865-546-4578 -----Original Message----- From: Ian Skinner [mailto:[email protected]] Sent: 2009-02-23 11:19 AM To: sql Subject: Re: Oracle: Find 'date' values that are not dates. N K wrote: > Check the following statement > select * from table1 where lastupdate <> to_date(lastupdate,'DD-mm-YY') I gave this a try, and still no go. First I looked at your applic_dt <> to_date(applic_dt,'mmddyy') and though that would return true for every record because a string '040108' is never going to equal a date '2008-04-01' value. So I modified it to try applic_dt <> to_char(to_date(applic_dt,'mmddyy'),'mmddyy') but I still get the same error: 'SQL Error: ORA-01843: not a valid month'. So the problem is still that there are data that can not be converted to a date value with the to_date function and the database throws an error when it process the first one of these invalid records. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3198 Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
