3

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

I want to create a function that get this date check it for not null and then change it to yyyy/mm/dd format

but the problem is that oracle doesn't accept dd-MMM-yy formate date as a input parameter and it say : Please Use yyyy-MM-dd date format !!!

how can I first change dd-MMM-yy formate to yyyy-MM-dd so oracle accept it as input then change it to yyyy/mm/dd

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
OmiD
  • 231
  • 2
  • 4
  • 14
  • What is the exact error? How are you inputing the data? – Mureinik Feb 03 '15 at 12:24
  • Also in oracle MMM is MON when dealing with date format strings. – Asheliahut Feb 03 '15 at 12:27
  • 6
    "*I have a date field in a table that contains date in dd-MMM-yy format*" - no you don't. Dates don't have "a format". Any format you see is applied by the SQL client you use. To change the formatting of the output, configure your SQL client appropriately. –  Feb 03 '15 at 12:31
  • 1
    What is the datatype of your field? Is it DATE or VARCHAR2? If the former, then you don't need to do anything, as you don't control the way that Oracle stores DATE values (you can only control how you view them and how you pass dates-as-a-string into the field/variable). As others have requested, we need more information. What have you written so far. What is your table definition? What is the exact error message? – Boneist Feb 03 '15 at 12:42
  • @ Boneist the format is date – OmiD Feb 03 '15 at 12:46
  • @OmiD, did you try the suggestion given in the answer. – Lalit Kumar B Feb 03 '15 at 13:35
  • 1
    @Lalit I am trying to solve the problem , tnx for your complete answer – OmiD Feb 03 '15 at 14:56

1 Answers1

12

: Please Use yyyy-MM-dd date format !!!

That is no way related to an Oracle error.

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

No, you are confused. Oracle does not store dates in the format you see. It stores it internally in 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 proper FORMAT MODEL.

While inserting, use TO_DATE with proper FORMAT MODEL.

What you see as a format by default, is your locale specific NLS 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.

By MMM if you mean the month name up to three characters, then use MON.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • 1
    Kumar your complete answer help me to change my view, and finally I did the job but in other way – OmiD Feb 07 '15 at 06:43
  • here goes my 2 cents for those wondering wich are the date formats see (a little down into the page): [https://docs.oracle.com/en/database/oracle/oracle-database/20/sqlrf/Format-Models.html#GUID-49B32A81-0904-433E-B7FE-51606672183A](https://docs.oracle.com/en/database/oracle/oracle-database/20/sqlrf/Format-Models.html#GUID-49B32A81-0904-433E-B7FE-51606672183A) – Paulo Bueno Nov 25 '20 at 12:39