Dates in Oracle Database

Introduction

Oracle database stores dates in an internal numeric format, representing the century, year, month, day, hours, minutes, and seconds. The default display and input format for any date is DD-MON-YY(You can change it). Valid Oracle dates are between January 1, 4712 B.C. and December 31, 9999  .

Oracle database supports simple date(True Date) and time(Date and Time) which stores in standard internal format.
Oracle supports a set of built ins function for manipulating date and time.

Date Format

As we mentioned before that oracle stores Dates internal in numeric format, but in displaying dates it displays it in different formats.
The default date format is "DD-MON-YY", the conversion of formats is done by TO_CHAR function that has below syntax

 TO_CHAR(DATE,'DATE_FORMAT','NLS_PARAMETERS');  

Date : is date value
NLS_PARAMETERS : is an optional and determine different NLS parameters in conversion.
DATE_FORMAT: It contains different format from below table

Parameter	Explanation
YEAR	Year, spelled out alphabetically
YYYY	4-digit year
YYY YY Y	Last 3, 2, or 1 digit(s) of year.
IYY IY I	Last 3, 2, or 1 digit(s) of ISO year.
IYYY	4-digit year based on the ISO standard
RRRR	Accepts a 2-digit year and returns a 4-digit year. A value between 0-49 will return a 20xx year. A value between 50-99 will return a 19xx year.
Q	Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
MM	Month (01-12; JAN = 01).
MON	Abbreviated name of the month.
MONTH	The name of month, padded with blanks to length of 9 characters.
RM	Roman numeral month (I-XII; JAN = I).
WW	The week of the year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W	The week of the month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW	The week of year (1-52 or 1-53) based on the ISO standard.
D	Day of the week (1-7). Sunday is day 1 when nls_territory is set to 'AMERICA' but differs if another nls_territory is set (i.e. 'UNITED KINGDOM' or 'GERMANY' - in these cases Monday is 1.
DAY	Name of the day.
DD	The day of month (1-31).
DDD	The day of year (1-366).
DY	Abbreviated name of the day. (Mon, Tue, Wed, etc)
J	Julian day; the number of days since January 1, 4712 BC.
HH	Hour of day (1-12).
HH12	Hour of day (1-12).
HH24	Hour of day (0-23).
MI	Minute (0-59).
SS	Second (0-59).
SSSSS	Number of seconds past midnight (0-86399).
FF	Fractional seconds. Use a value from 1 to 9 after FF to indicate the number of digits in the fractional seconds. For example, 'FF5'.
AM, A.M., PM, or P.M.	Meridian indicator
AD or A.D	AD indicator
BC or B.C.	BC indicator
TZD	Daylight savings identifier. For example, 'PST'
TZH	Time zone hour.
TZM	Time zone minute.
TZR	Time zone region.

Note that any text rather than date formats used in Date formats will be printed as text like - , / , "of", "Any Text between double quotation" ,  ..... etc.
Examples

 SELECT TO_CHAR (SYSDATE, 'DD/MM/YYYY') FROM DUAL;  
 --15/06/2012  
   
 SELECT TO_CHAR (SYSDATE, 'Month DD, YEAR') FROM DUAL;  
  -- June    17, TWENTY TWELVE  
   
 SELECT TO_CHAR (SYSDATE, 'FMMonth DD, FMYEAR') FROM DUAL;  
 -- June 17, TWENTY TWELVE  
   
 SELECT TO_CHAR (sysdate, ' DDth "of" MON') FROM DUAL;  
 -- 15TH of JUNE  
   
 SELECT TO_CHAR (sysdate, 'FMDay, DDth Month YYYY', 'NLS_DATE_LANGUAGE=Dutch')  
  FROM DUAL;  
 -- Vrijdag, 15TH Juni 2012  
   
 SELECT TO_CHAR (SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;  
 -- 2012-06-15 11:14:59  
   
 SELECT TO_CHAR (SYSDATE, 'FMDdspth "of" Month YYYY FMHH:MI:SS AM') FROM DUAL;  
 --Fiveteenth of June 2012 11:16:17 AM  

Get Current Date and Time.

We always need to get current date and time in our code, Oracle produce SYSDATE, CURRENT_DATE, LOCALTIMESTAMP, SYSTIMESTAMP, CURRENT_TIMESTAMP functions for this purpose.

  --Get True Date from oracle database server  
  SELECT SYSDATE FROM DUAL;   
  -- 15-Jun-12 10:20:00 AM   
   
    
  --Get True date from oracle session timezone  
  SELECT CURRENT_DATE FROM DUAL;   
  --15-Jun-12 10:20:00 AM   
   
   
 --Get Timestamp with timezone from oracle database server    
  SELECT SYSTIMESTAMP FROM DUAL;   
  --15-Jun-12 10:20:00.974253 AM +03:00  
    
    
 --Get Timestamp from oracle session timezone    
  SELECT LOCALTIMESTAMP FROM DUAL;   
  --15-Jun-12 10:20:00.974253 AM   
   
   
 --Get Timestamp with timezone from oracle session timezone    
  SELECT CURRENT_TIMESTAMP FROM DUAL;  
  --15-Jun-12 10:20:00.974253 AM +03:00  

Date Conversions

We use TO_DATE, TO_TIMESTAMP to convert string values to date or timestamp, their syntax is same as TO_CHAR that was mentioned before.

Examples

 SELECT TO_DATE ('2012/06/18', 'yyyy/mm/dd') FROM DUAL;  
   
 SELECT TO_TIMESTAMP ('08-Jun-12 05:34:34.123456', 'DD-Mon-RR HH24:MI:SS.FF')  
  FROM DUAL;  

Date Built-ins Functions

Oracle produce a set of built-ins functions for handling different operation in Date data type

1- ADD_MONTHS(date DATE , n INTEGER) 

It adds ( n ) number of months to date

2- GREATEST( d1 DATE, d2 DATE, d3 DATE , ....... )

It return the latest date of d1, d2, d3, .......

3- LEAST( d1 DATE, d2 DATE, d3 DATE , ....... )

It return the earliest date of d1, d2, d3, .......

4- LAST_DAY( d1 DATE)

It return date of last day of month in d1 date.

5- MONTHS_BETWEEN( d1 DATE, d2 DATE)

It return the number of months separating two days.
Note d1 must be the later date and d2 is the earlier date.

6- NEXT_DAY( date DATE, day VARCHAR2)

It return the date of next day appropriate to day

day are any value from (SUN, MON, TUE, WED, THU, FRI, and SAT)

Example : SELECT NEXT_DAY (SYSDATE, 'FRI') FROM DUAL;

7- TRUNC(d Date, fmt Varchar2)

It trunc date to specific format

Example : SELECT TRUNC(SYSDATE,'MM') FROM DUAL;

8- ROUND( d Date, fmt Varchar2)

It rounds date to specific format

Example : SELECT ROUND(SYSDATE,'MM') FROM DUAL;

9- EXTRACT ( YEAR / MONTH / WEEK / DAY / HOUR / MINUTE / TIMEZONE  FROM DATE)

It extract specific portion of date and time from date
The portion that you can extract from date is ( year, month, week, day, hour, minute, timezone).

Example :

SELECT EXTRACT (MINUTE FROM SYSTIMESTAMP) FROM DUAL;

SELECT EXTRACT (YEAR FROM SYSDATE) FROM DUAL;

You can also use other functions like FLOOR, CEIL, DUMP, MIN, MAX.

Date and Time Calculations
1- Date and time can be used in arithmetic operations( addition and subtract) only.

2- You can compare dates in logical conditions using ( = , <> , != , > , >= , < , <= , ... etc ).

3- You can add and subtract number from date and that will yield date also
     Example : SELECT SYSDATE + 5 FROM DUAL;

4- You can subtract two dates and the result will be the number of days between two days.
     Example : SELECT SYSDATE - SYSDATE FROM DUAL;
     Note : you can not add two dates.

Thanks
Mahmoud A. El-Sayed