date function not recognized :(
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: date function not recognized :(

  1. #1
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681

    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

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    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
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  3. #3
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    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

  4. #4
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    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"

  5. #5
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    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

  6. #6
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    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"

  7. #7
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    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

  8. #8
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    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

  9. #9
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    i tried it dear...but still same error :(
    Behind The Success And Failure Of A Man Is A Woman

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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
  •  



Click Here to Expand Forum to Full Width