Oracle: Find 'date' values that are not dates.

[Ian Skinner-3]

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


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:3193
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[N K-2]

Are you using SQL server OR oracle?

>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

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:3194
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[N K-2]

Sorry the subject does specify that its Oracle ...missed it

>Are you using SQL server OR oracle?
>
>
>>Ian

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:3195
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[N K-2]

Check the following statement
select * from table1 where  lastupdate <> to_date(lastupdate,'DD-mm-YY')


Nk

>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

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:3196
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Ian Skinner-3]

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:3197
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6

[Raymond Thompson]

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:HOF@...]
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=17837.14401.6

[Mike Kear-2]

My hosting service uses SQLServer2005.  I just had a client send me the
following question:

[quote]
I have just installed SQL server 2008.  Been trying to connect to the AFP
SQL server with the new management Studio.  I keep getting a permission
error.  Do you know if I can look at the SQL server 2005 database with this
tool.
[/quote]

Does anyone know if there's a 'gotcha' connecting to SQL2005 with the
SQL2008 tool?    I haven't got 2008 yet so I'm not in a position to test it
for him.

Or, alternatively,  is there anyone here with 2008 that would help me by
connecting to one of my databases to check if it's possible?

Cheers
Mike Kear
Windsor, NSW, Australia
0422 985 585
Adobe Certified Advanced ColdFusion Developer
AFP Webworks Pty Ltd
http://afpwebworks.com 
Full Scale ColdFusion hosting from A$15/month



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:3199
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.6