DATE formatting in oracle

Question

I have a date field in a table that contains a date in the format dd-MMM-yy.

I want to create a function that will receive this date, to check it for zero, and then change it to yyyy / mm / dd

but the problem is that the oracle does not accept dd-MMM-yy format date as input parameter and it says: Please use yyyy-MM-dd date format !!!

how can I change dd-MMM-yy formate to yyyy-MM-dd first so that the oracle accepts it as an input and then changes it to yyyy / mm / dd

Answer 1

: Please use the date format yyyy-MM-dd !!!

This has nothing to do with Oracle error.

I have a date field in a table that contains a date in the format dd-MMM-yy.

No, you are confused. Oracle does not save dates in the format you see. It stores it inside 7 bytes with each byte storing different components of the datetime value.

Byte Description ---- ------------------------------------------------- 1 Century value but before storing it add 100 to it 2 Year and 100 is added to it before storing 3 Month 4 Day of the month 5 Hours but add 1 before storing it 6 Minutes but add 1 before storing it 7 Seconds but add 1 before storing it 

If you want to display, use TO_CHAR with the appropriate FORMAT .

When pasting, use TO_DATE with a suitable MODEL FORMAT .

What you see as the default format is your locale-specific locale settings.

 SQL> select parameter, value from v$nls_parameters where parameter='NLS_DATE_FORMAT'; PARAMETER VALUE --------------- ---------------------------------------------------------------- NLS_DATE_FORMAT DD-MON-RR SQL> select sysdate from dual; SYSDATE --------- 03-FEB-15 SQL> select to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss') from dual; TO_CHAR(SYSDATE,'MM ------------------- 02/03/2015 17:59:42 SQL> 

Update regarding MMM format.

For MMM, if you mean the name of the month up to three characters, use MON .