Formatting DATE in oracle

by

: 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.

More Related Contents:

  1. How are dates stored in Oracle?
  2. Using date in a check constraint, Oracle
  3. Oracle’s default date format is YYYY-MM-DD, WHY?
  4. Convert epoch to date in sqlplus / Oracle
  5. Oracle: how to add minutes to a timestamp?
  6. Valid Date Checks in Oracle
  7. ORA-01830: date format picture ends before converting entire input string / Select sum where date query
  8. How to get the week day name from a date?
  9. How to convert “1985-02-07T00:00:00.000Z” (ISO8601) to a date value in Oracle?
  10. ANYONE CAN TELL ME THAT WHY THIS CODE IS GIVING ME AN ERROR OF TABLE OT CLUSTER KEY WORD IS MISSING? [closed]
  11. How do I escape a reserved word in Oracle?
  12. ORA-30926: unable to get a stable set of rows in the source tables
  13. ORA-12514 TNS:listener does not currently know of service requested in connect descriptor
  14. “Safe” TO_NUMBER()
  15. When or Why to use a “SET DEFINE OFF” in Oracle Database
  16. error: ORA-65096: invalid common user or role name in oracle
  17. Oracle insert if not exists statement
  18. Error : ORA-01704: string literal too long
  19. Oracle client ORA-12541: TNS:no listener [closed]
  20. Does Oracle have an equivalent of SQL Server’s table variables?
  21. Reason why oracle is case sensitive?
  22. Oracle date difference to get number of years
  23. SQL Fiddle Output Error
  24. Trying to export a Oracle via PL/SQL gives a date of 0000-00-00
  25. How to drop multiple interval partitions based on date?
  26. How to list active / open connections in Oracle?
  27. How to call an Oracle function with a Ref Cursor as Out-parameter from C#?
  28. How to Register Pluggable Database(PDB) with new created LISTENER
  29. Grant Select on all Tables Owned By Specific User
  30. ORA-12704: character set mismatch

Leave a Comment