not a valid month
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: not a valid month

  1. #1
    Join Date
    Jun 2001
    Posts
    109
    I am getting this erro ORA-01843 not a valid month.
    I am trying to convert the date from MM-DD-YYYY format to DD-MON-YY its giving me this error. Does anyone have any idea what to do to fix it?
    The user enters the date in MM-DD-YYYY format whereas in database the date format is DD-MON-YY. I need to get the sum amount when the date's are equal.

    here is the query..
    select sum(payin_amount) from ts_direct_payins
    where payin_Date = to_date('02-27-2002','MM-DD-YYYY')
    and cr_dr_flag ='D'

    This works fine in SQL but not on FORMS.
    Please let me know at the earlist.

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    This SQL not from FORMS.
    Can u send SQL from FORMS?

  3. #3
    Join Date
    Jun 2001
    Posts
    109
    That Sql was written in When_button_pressed trigger in forms
    and the PREFORM trigger has this
    forms_ddl('ALTER SESSION SET NLS_DATE_FORMAT = ''DD-MON-YY''');

    This is written in W-B-P

    select sum(payin_amount)into sum_deb_amount from ts_direct_payins
    where payin_Date =to_datE(ayin_date ,'DD-MON-YY')
    and cr_dr_flag ='D';

    select sum(payin_amount)into sum_cr_amount from ts_direct_payins
    where payin_Date =to_date(ayin_date ,'Dd-MON-YY')
    and cr_dr_flag ='C';

    ayin_date format: MM-DD-YYYY'

  4. #4
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    In ur query u have:
    1)
    Oayin_date --> char/varchar2 in format MM-DD-YYYY
    and u should use:--> where payin_Date =to_date(Oayin_date, 'MM-DD-YYYY')
    or
    2)
    Oayin_date --> date variable
    and u should use:--> where payin_Date = Oayin_date


  5. #5
    Join Date
    Jun 2001
    Posts
    109
    Payin_Date has date as datatype and format_mask 'mm-dd-yyyy'

    so I gave my query as...

    select sum(payin_amount)into sum_deb_amount from ts_direct_payins
    where payin_Date =ayin_date and cr_dr_flag ='D';


    but its still not displaying the sum.

  6. #6
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Ur problem not inDATE_FORMAT.
    Are u use sysdate for insert information (Payin_Date/Oayin_Date)
    if yes then:
    select...
    ..
    where trunc(payin_Date) = trunc(Oayin_date )
    ...
    U should align both date to midnight before compare its.

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