Oracle Date and Timestamp

Date Format Models To get the date

Select to_char(sysdate, 'DD') "Today Date" FROM DUAL;

Select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') "Today Date with Time" FROM DUAL;

Below is a comprehensive List of Date Format Models

FORMAT MEANING
D                Day of the week
DD               Day of the month
DDD              Day of the year
DAY              Full day for ex. ‘Monday’, ’Tuesday’, ’Wednesday’
DY               Day in three letters for ex. ‘MON’, ‘TUE’,’FRI’
W                Week of the month
WW               Week of the year
MM               Month in two digits  (1-Jan, 2-Feb,…12-Dec)
MON              Month in three characters like “Jan”, ”Feb”, ”Apr”
MONTH            Full Month like “January”, ”February”, ”April”
RM               Month in Roman Characters (I-XII, I-Jan, II-Feb,…XII-Dec)
Q                Quarter of the Month
YY               Last two digits of the year.
YYYY             Full year
YEAR             Year in words like “Nineteen Ninety Nine”
HH               Hours in 12 hour format
HH12             Hours in 12 hour format
HH24             Hours in 24 hour format
MI               Minutes
SS               Seconds
FF               Fractional Seconds
SSSSS            Milliseconds
J                Julian Day i.e Days since 1st-Jan-4712BC to till-date
RR               If the year is less than 50 Assumes the year as 21ST Century.
                 If the year is greater than 50 then assumes the year in 20th Century.
AM               Specifies the date and Time with AM/PM
RR & RRRR

These are better explained with the below example For RR and RRRR format If the specified two-digit year is 00 to 49, then If the last two digits of the current year are 00 to 49, then the returned year has the same first two digits as the current year.

SELECT TO_DATE ('010117', 'MMDDRR') AS date_rr,
       TO_DATE ('010117', 'MMDDRRRR') AS date_rrrr
  FROM DUAL;

date_rr                  date_rrrr
---------------------------------
01-JAN-2017       01-JAN-2017

If the last two digits of the current year are 50 to 99, then the first 2 digits of the returned year are 1 less than the first 2 digits of the current year.

SELECT TO_DATE ('010195', 'MMDDRR') AS date_rr,
       TO_DATE ('010195', 'MMDDRRRR') AS date_rrrr
  FROM DUAL;

date_rr                  date_rrrr
--------------------------------
01-JAN-1995       01-JAN-1995

Date Suffixes

TH         Returns th, st, rd or nd according to the leading number like 1st , 2nd 3rd 4th
select to_char(sysdate,'Day ddth Month yyyy') "Today" from dual;

SP         Spells out the leading number
select to_char(sysdate,'Day ddsp Month yyyy') "Today" from dual;

SPTH     Returns Spelled Ordinal number. For. Example First, Fourth
select to_char(sysdate,'Day ddspth Month yyyy') "Today" from dual;

AM or PM Returns AM or PM according to the time
select to_char(sysdate,'DD-MON-YYYY HH:MI:SS AM') "Today" from dual;

Some Basic Date Functions To select a date one month before and one month later from today date

Select add_months(sysdate, -1) as prev_month , sysdate, add_months (sysdate, 1) as next_month
from dual;

To select last date of current month

select sysdate, last_day(sysdate) as last_day_curr_month,
last_day(sysdate) + 1 as first_day_next_month
from dual;

To add 'n' months to current month

SELECT ADD_MONTHS(SYSDATE,2) FROM DUAL;

To find the months between 2 dates -- If the first date is larger than the second date, a positive number is returned else negative. --It the date is last day of the month for both the parameters a whole number is returned.

SELECT MONTHS_BETWEEN(sysdate + 300, sysdate) from dual;

The NEXT_DAY function returns the date for the day specified in second parameter, following the date mentioned in the first parameter

SELECT NEXT_DAY(SYSDATE,'WEDNESDAY') FROM DUAL;

ROUND is similar to the NUMERIC ROUND FUNCTION AND it rounds the date to the nearest --minute, hour, date, week, month, year, century --before or after the second parameter passed. example

SELECT TO_CHAR(ROUND(TO_DATE('25-JUL-2016 10:05:37','DD-MON-RRRR HH24:MI:SS'),'DD'), 'DD-MON-RRRR HH24:MI:SS') FROM DUAL;  returns [25-JUL-2016]
SELECT TO_CHAR(ROUND(TO_DATE('25-JUL-2016 17:05:37','DD-MON-RRRR HH24:MI:SS'),'DD'), 'DD-MON-RRRR HH24:MI:SS') FROM DUAL;  returns [26-JUL-2016]

If second parameter passed is DD

25-JUL-2016 before noon returns 25-JUL-2016
25-JUL-2016 after noon returns 26-JUL-2016

If second parameter passed is YEAR

25-JUL-2016 returns 01-JAN-2017

Below are some of the formats for which the date can be rounded. You can replace the second parameter with any of the values below to check.

Format Mask                               Rounds of to Nearest

CC or SSC                                  Century
SYYY, YYYY, YEAR,      
SYEAR, YYY, YY, or Y                       Year on July 1
IYYY, IYY, IY, or I                        Standard ISO year
Q                                          Quarter on the sixteenth day of the second month of the quarter
MONTH, MON, MM, or RM                      Month on the sixteenth day, which is not necessarily
                                                the same as the middle of the month
WW                                         Day of the week as the first day of the year
IW                                         Day of the week as the first day of the ISO year
W                                          Day of the week as the first day of the month
DDD, DD, or J                              Day
DAY, DY, or D                              Starting day of the week
HH, HH12, HH24                             Hour
MI                                         Minute

NEW_TIME gives the date and time of one timezone in another timezone

SELECT TO_CHAR(NEW_TIME(TO_DATE('29-JUL-2016 15:30:45', 'DD-MON-RRRR HH24:MI:SS'), 'AST', 'MDT'), 'DD-MON-RRRR HH24:MI:SS')
FROM DUAL;

The Time Zones and their abbreviations are given below

Time Zone        Abbreviation Description

AST                  Atlantic Standard Time
ADT                  Atlantic Daylight Time
BST                  Bering Standard Time
BDT                  Bering Daylight Time
CST                  Central Standard Time
CDT                  Central Daylight Time
EST                  Eastern Standard Time
EDT                  Eastern Daylight Time
GMT                  Greenwich Mean Time
HST                  Alaska-Hawaii Standard Time
HDT                  Alaska-Hawaii Daylight Time
MST                  Mountain Standard Time
MDT                  Mountain Daylight Time
NST                  Newfoundland Standard Time
PST                  Pacific Standard Time
PDT                  Pacific Daylight Time
YST                  Yukon Standard Time
YDT                  Yukon Daylight Time

TRUNC
TRUNC is similar to floor and it rounds down the date to the beginning of the minute, hour, date, week, month, year, century based on the parameter passed. example

SELECT TO_CHAR(TRUNC(TO_DATE('21-JUL-2016 10:05:37','DD-MON-RRRR HH24:MI:SS'),'MM'), 'DD-MON-RRRR HH24:MI:SS') FROM DUAL;  -- Will return the first of July.

Parameters passed are similar to those passed for ROUND. To extract day, month, year from a given date

select extract(day from sysdate) as only_day from dual;
select extract(month from sysdate) as only_month from dual;
select extract(year from sysdate) as only_year from dual;

TIMESTAMP

SELECT SYSTIMESTAMP FROM DUAL; -- This gives the date and time with offset in Hours from UTC

SELECT TO_TIMESTAMP ('26-JUL-2016 14:10:10.123000789', 'DD-Mon-RRRR HH24:MI:SS.FF') FROM DUAL;

SELECT TO_TIMESTAMP ('26-JUL-2016 14:10:10', 'DD-Mon-RRRR HH24:MI:SS.FF') FROM DUAL;


TIMESTAMP with TIME ZONE -- This is a variant of TIMESTAMP which includes the time zone of a particular region.

SELECT TO_TIMESTAMP_TZ('2016-07-29 11:00:00', 'YYYY-MM-DD HH:MI:SS TZH:TZM') FROM DUAL;
output
29-JUL-2016 11.00.00.000000000 AM ASIA/CALCUTTA

Quote for the day
“Failure Will Never Overtake Me If My Determination To Succeed Is Strong Enough.”- Og Mandino