date function not recognized :(

Quote:

---

Originally Posted by pando

Try

select count(*) from (select * from emp where IS_DATE(BIRTHDATE)=0)
where
to_date(birthdate,'mmddyyyy')>=to_date('01-JAN-90', 'DD-MON-YYYY')

you must use to_date function, 01-JAN-90 is a varchar2 and you are comparing date with varchar, incorrect

if it still fails you may have some rubbish data

thanks

---

Maybe to_date('01-JAN-1990', 'DD-MON-YYYY')


;)

If you will insist on loading your dates into a varchar2, then at least use a decent format like YYYYMMDD.

yes pando:) i know i have rubbish data....thats why i am using the "is_date"
function to filter it. I don't understand why it still hit this rubbish data.

I know the 76th row was the rubbish data with value "00 19 ".
I found it by running:

select count(*) from emp where to_date(birthdate,'mmddyyyy') is not null
and rownum<76;
count(*)
---------
75

select count(*) from emp where to_date(birthdate,'mmddyyyy') is not null
and rownum<77;

ERROR at line 1:
ORA-01843: not a valid month

select birthdate from emp where is_date(birthdate)=1;

birthdate
--------
00 19

any other ideas how to bypass this?:)

Huh ! Ugly a?
Oks let's do the following
create or replace function my_to_date(d varhar2,fmt varchar2) return date as
dummy date;
begin
dummy:=to_date(d,fmt);
return dummy;
excedptions
when others then return NULL;
end my_to_date
select..... from emp
where my_to_date(birthdate,'mmddyyyy') is not null and my_to_date((birthdate,'mmddyyyy') > '01-JAN-1990'

P.S. It is better to catch not when others but when your error, but I am too lazy to do it

Cheers

Quote:

---

Originally Posted by yxez

select count(*) from EMP where
to_date(birthdate,'mmddyyyy')>='01-JAN-90';

---

This is so funny, believe me, "date function" works.

Why you don't start by looking at how each one of the elements of your predicate are resolved?

Something as simple as...

Code:

---

select  to_date(birthdate,'mmddyyyy') from EMP where rownum < 2;
select  to_date('01011990','mmddyyyy') from dual;
select  to_date('01-JAN-90', 'dd-mon-yy') from dual;

---

What do you see?