Date Formatting Issue

Hi All,

I need your assistance.

I have the below code in a query but I get the an error due to date formatting.

Can anyone please help?


case
when to_date(:p_Enter_Date,'DD/MMM/YY') - MAX(st.sales_date)   > 180 –180 days
then 'Y'
else 'N'
END                                                               as Late_Sales_Payment


ORA-01821: date format not recognized
01821. 00000 -  "date format not recognized"
jose11auAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
chaauConnect With a Mentor Commented:
You should be fine with your statement (I guess " - 180 days" was just a comment). Just fix the format.

case
when to_date(:p_Enter_Date,'DD/MON/YY') - MAX(st.sales_date)   > 180
then 'Y'
else 'N'
END                                                               as Late_Sales_Payment

Open in new window


BTW, "MON" format is for abbreviated month name, like Jan, Feb,  Mar, etc.
If your p_Enter_Date takes another format (i.e dd/mm/yyyy) you need to use 'DD/MM/YYYY'.

BTW, did you know, that format mask part of this function is optional. You could just use to_date(:p_Enter_Date) and let Oracle to figure out what the user has entered. It is not a bulletproof option, but it will work for any fancy format users may come up with
0
 
chaauCommented:
Use to_date(:p_Enter_Date,'DD/MON/YY')

See here for all supported date formats
0
 
jose11auAuthor Commented:
Hi All,

I forgot to explain what I am trying to archive in above case statement

If :p_Enter_Date- MAX(fx.entry_date) > 180 days then 'Y' else 'N'

Thanks
0
 
jose11auAuthor Commented:
Thanks so much, no more error.

Cheers
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.