Re: Oracle Query

From: rjamya <rjamya_at_gmail.com>
Date: Thu, 13 Jan 2005 09:47:18 -0500
Message-ID: <9177895d0501130647363aa46@mail.gmail.com>

John,

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 ....
Raj

On Thu, 13 Jan 2005 14:18:17 -0000, MA015b8234 <ma015b8234_at_blueyonder.co.uk> 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 =
'MANDATORY';
--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jan 13 2005 - 08:46:46 CST