Re: Oracle Query

  • From: rjamya <rjamya@xxxxxxxxx>
  • To: MA015b8234 <ma015b8234@xxxxxxxxxxxxxxxx>
  • Date: Thu, 13 Jan 2005 09:47:18 -0500


use translate function to see which rows have non-numeric data... and
then exclude those ...

something like 

select blah
from (select blah
            from my_table
           where translate(date_as_char,'0123456789','*') = '********')
where to_date(date_as_char) ....

the translate will let you select only those rows that are purely
numeric, but it won't filter those that are purely numeric but not
valid dates ....

On Thu, 13 Jan 2005 14:18:17 -0000, MA015b8234
<ma015b8234@xxxxxxxxxxxxxxxx> wrote:
> I am trying to select data from a database based on date range which have
> been stored in a varchar2 8 characters field in the format yyyymmdd. When I
> try to use to_date function to convert the data into Oracle dates I get a
> error indicating that there is non-numeric data stored in the column.
> However, after checking several times I didn't find any non-numeric data or
> null data.  I have tried using decode function but without any success.
> Any suggestions on how to fix this.
> John

select standard_disclaimer from company_requirements where category =

Other related posts:

  • » Re: Oracle Query