date function not recognized :(

Hi Friends,

I am doing this simple select command against my table EMP...

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

to_date(birthdate,'mmddyyyy')>='01-JAN-90'
*
ERROR at line 2:
ORA-01843: not a valid month


***birthdate column is varchar2(8) of format 'mmddyyyy' (to allow conversion errors loaded from
foxpro database)
To bypass erroneous dates, I created this function:
=====================
CREATE OR REPLACE FUNCTION is_date (p_string VARCHAR2)
RETURN INTEGER
AS
v_dummy DATE;
BEGIN
v_dummy := TO_DATE(p_string, 'MMDDYYYY');
RETURN 0;
EXCEPTION
WHEN OTHERS THEN RETURN 1;
END;
/
======================

Then I run my second program.....

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

to_date(birthdate,'mmddyyyy')>='01-JAN-90' AND IS_DATE(BIRTHDATE)=0
*
ERROR at line 2:
ORA-01843: not a valid month


....WHY IS IT THAT MY FUNCTION "IS_DATE" NOT WORKING?


THANKS A LOT

Quote:

---

Originally Posted by yxez

Hi Friends,

I am doing this simple select command against my table EMP...

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

to_date(birthdate,'mmddyyyy')>='01-JAN-90'
*
ERROR at line 2:
ORA-01843: not a valid month

---

The date format you are giving doesn't match with the literal value.
Use either

Code:

---

to_date(birthdate,'mmddyyyy')>='01011990'

---

or

Code:

---

to_date(birthdate,'DD-MON-YY')>='01-JAN-90'

---

HTH

are u sure dear :confused:

the format is correct because if I test it this way...

select to_date('01012007','mmddyyyy') from dual;

to_date('
---------
01-JAN-07

and my birthdate column is varchar2(8) and not a date column:)

Code:

---

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create table t1 (c1 varchar2(8));

Table created.

SQL> insert into t1 values ('010190');

1 row created.

SQL> insert into t1 values ('010290');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1 where to_date(c1,'MMDDYY') >= to_date('010190','MMDDYY');

C1
--------
010190
010290

SQL>

---

Sanjay G. ,sorry but what u posted is definitely wrong.
The format in the TO_DATE has nothing to deal with the format of the string at the right side of the greater operator.
yxez, pls post here the results of
show parameter nls
also try
....
where to_date(birthdate,'mmddyyyy')>=to_date('01.01.1990','dd.mm.yyyy')
and let us know if it works

Regards
Boris

My bad. Just realized. The example above should work though.

Hi boris dear:) ....

SQL> show parameter nls

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_calendar string
nls_comp string
nls_currency string
nls_date_format string
nls_date_language string
nls_dual_currency string
nls_iso_currency string
nls_language string AMERICAN
nls_length_semantics string BYTE
nls_nchar_conv_excp string FALSE
nls_numeric_characters string

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_sort string
nls_territory string AMERICA
nls_time_format string
nls_time_tz_format string
nls_timestamp_format string
nls_timestamp_tz_format string
SQL>


Hmnnnn .....my error which is "invalid month" means that my
birthdate data contains erroneous data like for example '13012007' in which
13 is out of range for month.
I am just wondering my the function I used to filter the data in the
where clause (is_date) was not working?

Thanks....

Jenn

I see now. Sorry for not reading careful the question.
Well seems it depends if the to_date will get evaluated first or is_date will be evaluated first. Obviously to_date gets evaluated first :-(
Maybe u can try
select count(*) from (select * from emp where IS_DATE(BIRTHDATE)=0)
where
to_date(birthdate,'mmddyyyy')>='01-JAN-90'

Cheers

i tried it dear...but still same error :(

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

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?