not a valid month

[saritha]

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.

[Shestakov]

This SQL not from FORMS.
Can u send SQL from FORMS?

[saritha]

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'

[Shestakov]

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

[saritha]

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.

[Shestakov]

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.