Try this: 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;
On Tue, Feb 17, 2009 at 1:37 PM, Ian Skinner <[email protected]> wrote: > > I have a 6 numeric character field in a table that is supposed to be a > date value in the format of 'mmddyy'. But a small percentage (5 out of > 2.5 million for my first sampling) are not actually data recognizable as > a legitimate date. > > Is there any way I can easily query the data for records with a value > that can *NOT* be converted into a date in the format of 'mmddyy'? In > other words is their anyway to do something like an exception trap on > the to_date(field,'mmddyy') function to return records that will fail it? > > TIA > Ian > > P.S. Reposting as my first posting to the SQL list disappeared. > Appearing neither in my e-mail or on the web portal. > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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/cf-talk/message.cfm/messageid:319440 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
