-
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
Last edited by yxez; 06-20-2007 at 12:40 AM.
Behind The Success And Failure Of A Man Is A Woman
-
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
Sanjay G.
Oracle Certified Professional 8i, 9i.
"The degree of normality in a database is inversely proportional to that of its DBA"
-
are u sure dear
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
Behind The Success And Failure Of A Man Is A Woman
-
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.
Oracle Certified Professional 8i, 9i.
"The degree of normality in a database is inversely proportional to that of its DBA"
-
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.
Sanjay G.
Oracle Certified Professional 8i, 9i.
"The degree of normality in a database is inversely proportional to that of its DBA"
-
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
Behind The Success And Failure Of A Man Is A Woman
-
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 :(
Behind The Success And Failure Of A Man Is A Woman
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|