In Oracle Date data is stored in fixed-length fields of seven bytes each, corresponding to century, year, month, day, hour, minute, and second. (Also Read Formatting date in SQL (Oracle) -Simple date forma…) .The date and timestamp types and the formatting functions could be the most used functions of pl/sql.

The standard Oracle date format is ‘DD-MON-YY’ for example ’13-Nov-08′.

How to change this default date format for an oracle instance ?

NLS_DATE_FORMAT specifies the default date format to use with the TO_CHAR and TO_DATE functions.
This is specified oin the in the parameter file(init.ora).

For example
NLS_DATE_FORMAT = “MM/DD/YYYY”

The default value of this parameter is determined by NLS_TERRITORY.
During a user session user can change this with the ALTER SESSION statement.

For example

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS'

How to enter dates that are not in standard Oracle date format?

Use the TO_DATE function with a format mask,For example

SQL> select unique TO_DATE ('November 13, 1992', 'MONTH DD, YYYY') mydate from tab;
MYDATE
11/13/1992
SQL>

Related Topics,
Formatting date in SQL (Oracle) -Simple date forma…

Also Read,

• Database Schema | Oracle Schema Objects | What is database schema?
• Autonomous Transactions in PL/SQL
• SQL – Transaction Statements -Transaction Manageme…

• Savepoints In SQL Transactions. – SAVEPOINT
• Oracle Tablespace, and Datafiles – Introduction
• Formatting date in SQL (Oracle) -Simple date forma…
• FINDING database objects, finding valid and INVALI…
• Oracle Data types

Technorati Tags:
DATE Datatype in Oracle|Oracle Dates and Times|Dates in Oracle