DATE Data type in Oracle|Oracle Dates and Times|Dates in OracleDATE Data type in Oracle|Oracle Dates and Times|Dates in Oracle

0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Buffer 0 Email 0 Filament.io 0 Flares ×

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,

Technorati Tags:

0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Buffer 0 Email 0 Filament.io 0 Flares ×

One thought on “DATE Data type in Oracle|Oracle Dates and Times|Dates in OracleDATE Data type in Oracle|Oracle Dates and Times|Dates in Oracle

  1. Pingback: oracle date difference | date difference in sql and PL/SQL | SQL and PLSQL

Leave a Reply

Your email address will not be published. Required fields are marked *

Paged comment generated by AJAX Comment Page