Oracle Date and time stampe

In and around Oracle Application, as a developer you need to play with Dates while writing your code. Playing with dates is fun, although sometimes we faced some challenging tasks. Therefore it is always helpful to go through this feature of Oracle in detail. I did the same and here is what I have found.
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. Valid Oracle dates are between January 1, 4712 B.C. and December 31, 9999 A.D.
Unlike other datatypes, DATE datatypes are bit complex. However, Oracle Database and PL/SQL provide a set of true date and time datatypes that store both date and time information in a standard internal format, and they also have an extensive set of built-in functions for manipulating the date and time.
Get the current date and time:
Often it is required to retrieve the current date and time in our code and use them. Many developers go with SYSDATE function, but Oracle Database now offers several other functions as well.

SELECT SYSDATE FROM DUAL;

--27-FEB-12 (You will get the DATE from Oracle Database Server)

SELECT CURRENT_DATE FROM DUAL;

--27-FEB-12 (You will get the Current DATE from Oracle Session Time Zone)

SELECT LOCALTIMESTAMP FROM DUAL;

--27-FEB-12 01.16.42.486809000 AM (You will get the TIMESTAMP from Oracle Session Time Zone)

SELECT SYSTIMESTAMP FROM DUAL;

--27-FEB-12 01.19.41.059413000 AM -06:00 (You will get the TIMESTAMP WITH TIME ZONE from Oracle Database Server)

SELECT CURRENT_TIMESTAMP FROM DUAL;

--27-FEB-12 01.20.57.839733000 AM AMERICA/CHICAGO (You will get the TIMESTAMP WITH TIME ZONE from Oracle Session Time Zone)

DATE Format:

When a DATE value is displayed, Oracle must first convert that value from the special internal format to a printable string. The conversion is done by a function TO_CHAR, according to a DATE format. Oracle’s default format for DATE is “DD-MON-YY”. Whenever a DATE value is displayed, Oracle will call TO_CHAR automatically with the default DATE format. However, you may override the default behavior by calling TO_CHAR explicitly with your own DATE format.
Guidelines

• The format model must be enclosed in single quotation marks and is case sensitive.
• The format model can include any valid date format element. Be sure to separate the date value from the format model by a comma.
• The names of days and months in the output are automatically padded with blanks.
• To remove padded blanks or to suppress leading zeros, use the fill mode fm element.

Parameter	Explanation
YEAR	Year, spelled out
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
Q	Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
MM	Month (01-12; JAN = 01).
MON	Abbreviated name of month.
MONTH	Name of month, padded with blanks to length of 9 characters.
RM	Roman numeral month (I-XII; JAN = I).
WW	Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W	Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW	Week of year (1-52 or 1-53) based on the ISO standard.
D	Day of week (1-7).
DAY	Name of day.
DD	Day of month (1-31).
DDD	Day of year (1-366).
DY	Abbreviated name of day.
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	Seconds past midnight (0-86399).
FF	Fractional seconds.

SELECT TO_CHAR(SYSDATE, 'yyyy/mm/dd') FROM DUAL; -- Returns 2012/02/27

SELECT TO_CHAR(SYSDATE, 'Month DD, YYYY') FROM DUAL; -- Returns February  27, 2012

SELECT TO_CHAR(SYSDATE, 'FMMonth DD, YYYY') FROM DUAL; -- Returns February 27, 2012

SELECT TO_CHAR(SYSDATE, 'MON DDth, YYYY') FROM DUAL; -- Returns FEB 27TH, 2012

SELECT TO_CHAR(SYSDATE, 'FMMON DDth, YYYY') FROM DUAL; -- Returns FEB 27TH, 2012

SELECT TO_CHAR(SYSDATE, 'FMMonth DD, YYYY') FROM DUAL; -- Returns February 27, 2012

SELECT TO_CHAR(SYSDATE, 'FMDay, DDth Month YYYY') FROM DUAL; -- Returns Monday, 27TH February 2012

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL; -- Returns 2012-02-27 02:05:59

SELECT TO_CHAR(SYSDATE, 'FMDdspth "of" Month YYYY FMHH:MI:SS AM') FROM DUAL; -- Returns Twenty-Seventh of February 2012 02:06:36 AM

SELECT TO_CHAR(SYSDATE, 'FMDay, DDth Month YYYY','NLS_DATE_LANGUAGE=Spanish') FROM DUAL; -- Returns Lunes, 27TH Febrero 2012

SELECT TO_CHAR(SYSDATE, 'Day') TODAY_EN,
TO_CHAR(sysdate, 'Day', 'nls_date_language=Dutch') TODAY_DT
FROM DUAL; -- Returns Monday    Maandag

TO_DATE & TO_TIMESTAMP Functions:
 
TO_DATE function converts a character string representing a date to a date value according to the fmt specified. If fmt is omitted, the format is DD-MON-YY. The nlsparams parameter has the same purpose in this function as in the TO_CHAR function for date conversion.
TO_DATE(char,[fmt],[nlsparams])

The DD-MON-YY format is usually used to insert a date value. If a date must be entered in a format other than the default format, for example, with another century, or a specific time, you must use the TO_DATE function.

Examples:

SELECT TO_DATE('2012/02/27', 'yyyy/mm/dd') FROM DUAL;
SELECT TO_DATE('022712', 'MMDDYY') FROM DUAL;
SELECT TO_DATE('20120227', 'yyyymmdd') FROM DUAL;
SELECT TO_DATE('February 27, 2012, 04:00 P.M.','Month dd, YYYY, HH:MI A.M.')FROM DUAL;

Similarly TO_TIMESTAMP used to convert char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP datatype.

Examples:

 SELECT TO_TIMESTAMP('2012/FEB/27 04:12:34', 'YYYY/MON/DD HH:MI:SS') FROM DUAL;
SELECT TO_TIMESTAMP('February 27, 2012, 04:12:34 P.M.','Month dd, YYYY, HH:MI:SS A.M.')FROM DUAL;
SELECT TO_TIMESTAMP ('27-Feb-12 04:12:34.123000', 'DD-Mon-RR HH24:MI:SS.FF') FROM DUAL;

Arithmetic with Dates:

Here are 3 golden roles:

1. You can compare DATE values using the standard comparison operators such as =, !=, >, etc.
2. You can subtract two DATE values, and the result is a FLOAT which is the number of days between the two DATE values. In general, the result may contain a fraction because DATE also has a time component. For obvious reasons, adding, multiplying, and dividing two DATE values are not allowed.
3. You can add and subtract constants to and from a DATE value, and these numbers will be interpreted as numbers of days. For example, SYSDATE+1 will be tomorrow. You cannot multiply or divide DATE values.

Date Functions:
Date functions operate on Oracle dates. All date functions return a value of DATE data type except MONTHS_BETWEEN, which returns a numeric value.

• MONTHS_BETWEEN (date1, date2):
This function returns the number of months between date1 and date2. The result can be positive or negative. If date1 is later than date2, the result is positive; if date1 is earlier than date2, the result is negative. The noninteger part of the result represents a portion of the month.

• ADD_MONTHS(date, n):
This function adds n number of calendar months to date. The value of n must be an integer and can be negative.

• NEXT_DAY(date, ‘char’):
This function finds the date of the next specified day of the week (‘char’) following date. The value of char may be a number representing a day or a character string.

• LAST_DAY(date):
This function finds the date of the last day of the month that contains date.

• ROUND(date[,’fmt’]):
This function returns date rounded to the unit specified by the format model fmt. If the format model fmt is omitted, date is rounded to the nearest day.

• TRUNC(date[, ‘fmt’]):
This function returns date with the time portion of the day truncated to the unit specified by the format model fmt. If the format model fmt is omitted, date is truncated to the nearest day.

Examples:

 SELECT SYSTIMESTAMP FROM DUAL;  --Returns 27-FEB-12 05.16.41.676947000 AM -06:00
SELECT MONTHS_BETWEEN(SYSDATE , TO_DATE('01-JAN-2012','DD-MON-YYYY')) FROM DUAL; --Returns months between Sysdate and '01-JAN-2012' (1.84580906511350059737156511350059737157)
SELECT ADD_MONTHS (SYSDATE, 1) FROM DUAL; -- Move ahead one month (27-MAR-12)
SELECT ADD_MONTHS (SYSDATE, -4) FROM DUAL; -- Move backward four months (27-OCT-11)
SELECT NEXT_DAY (SYSDATE, 'MONDAY') FROM DUAL;  -- Go to next Monday after today’s date (05-MAR-12)
SELECT LAST_DAY (SYSDATE) FROM DUAL;  -- Returns the last day of the month (29-FEB-12)
SELECT ROUND (SYSDATE, 'MONTH') FROM DUAL; --01-MAR-12
SELECT TRUNC (SYSDATE, 'MONTH') FROM DUAL; --01-FEB-12
SELECT ROUND (SYSDATE, 'YEAR') FROM DUAL;  --01-JAN-12
SELECT TRUNC (SYSDATE, 'YEAR') FROM DUAL;  --01-JAN-12
SELECT ROUND (SYSDATE, 'DAY') FROM DUAL;   --26-FEB-12
SELECT TRUNC (SYSDATE, 'DAY') FROM DUAL;   --26-FEB-12

EXTRACT Function:
An EXTRACT datetime function extracts and returns the value of a specified datetime field from a datetime or interval value expression. When you extract a TIMEZONE_REGION or TIMEZONE_ABBR (abbreviation), the value returned is a string containing the appropriate time zone name or abbreviation
The syntax of EXTRACT function is
EXTRACT ( YEAR / MONTH / WEEK / DAY / HOUR / MINUTE / TIMEZONE  FROM DATE)

SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL; --Returns 2017

SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL; --Returns 3

SELECT EXTRACT(DAY FROM SYSDATE) FROM DUAL; --Returns 27

SELECT EXTRACT(HOUR FROM SYSTIMESTAMP) FROM DUAL; --Returns 24

SELECT EXTRACT(MINUTE FROM SYSTIMESTAMP) FROM DUAL; --Returns 41

TO FIND THE Seconds,Minutes,Hours

For the Seconds:-
-------------------

SELECT ('Your first Date'-'Your another Date') * DECODE( UPPER('SS')
, 'SS', 24*60*60
, 'MI', 24*60
, 'HH', 24, NULL )
FROM DUAL;

Example:-

SELECT (SYSDATE+1-SYSDATE) * DECODE( UPPER('SS')
, 'SS', 24*60*60
, 'MI', 24*60
, 'HH', 24, NULL )
FROM DUAL;

For Minutes:-
---------------

SELECT ('Your first Date'-'Your another Date') * DECODE( UPPER('MI')
, 'SS', 24*60*60
, 'MI', 24*60
, 'HH', 24, NULL )
FROM DUAL;

Example:-

SELECT (SYSDATE+1-SYSDATE) * DECODE( UPPER('MI')
, 'SS', 24*60*60
, 'MI', 24*60
, 'HH', 24, NULL )
FROM DUAL;

For the Hours:-
-----------------

SELECT ('Your first Date'-'Your another Date') * DECODE( UPPER('HH')
, 'SS', 24*60*60
, 'MI', 24*60
, 'HH', 24, NULL )
FROM DUAL;

Example:-

SELECT (SYSDATE+1-SYSDATE) * DECODE( UPPER('HH')
, 'SS', 24*60*60
, 'MI', 24*60
, 'HH', 24, NULL )
FROM DUAL;

select to_char(to_Date((sysdate-7),'DD-MM-RRRR HH24:MI:SS'),'MM/DD/RRRR') from dual

select to_char(to_Date(sysdate,'DD-MM-RRRR'),'YYYY/MM/DD HH:MM:SS') from dual
 

Time.ToString() Patterns
All the patterns:
0     MM/dd/yyyy     08/22/2006
1     dddd, dd MMMM yyyy     Tuesday, 22 August 2006
2     dddd, dd MMMM yyyy     HH:mm Tuesday, 22 August 2006 06:30
3     dddd, dd MMMM yyyy     hh:mm tt Tuesday, 22 August 2006 06:30 AM
4     dddd, dd MMMM yyyy     H:mm Tuesday, 22 August 2006 6:30
5     dddd, dd MMMM yyyy     h:mm tt Tuesday, 22 August 2006 6:30 AM
6     dddd, dd MMMM yyyy HH:mm:ss     Tuesday, 22 August 2006 06:30:07
7     MM/dd/yyyy HH:mm     08/22/2006 06:30
8     MM/dd/yyyy hh:mm tt     08/22/2006 06:30 AM
9     MM/dd/yyyy H:mm     08/22/2006 6:30
10     MM/dd/yyyy h:mm tt     08/22/2006 6:30 AM
10     MM/dd/yyyy h:mm tt     08/22/2006 6:30 AM
10     MM/dd/yyyy h:mm tt     08/22/2006 6:30 AM
11     MM/dd/yyyy HH:mm:ss     08/22/2006 06:30:07
12     MMMM dd     August 22
13     MMMM dd     August 22
14     yyyy'-'MM'-'dd'T'HH':'mm':'ss.fffffffK     2006-08-22T06:30:07.7199222-04:00
15     yyyy'-'MM'-'dd'T'HH':'mm':'ss.fffffffK     2006-08-22T06:30:07.7199222-04:00
16     ddd, dd MMM yyyy HH':'mm':'ss 'GMT'     Tue, 22 Aug 2006 06:30:07 GMT
17     ddd, dd MMM yyyy HH':'mm':'ss 'GMT'     Tue, 22 Aug 2006 06:30:07 GMT
18     yyyy'-'MM'-'dd'T'HH':'mm':'ss     2006-08-22T06:30:07
19     HH:mm     06:30
20     hh:mm tt     06:30 AM
21     H:mm     6:30
22     h:mm tt     6:30 AM
23     HH:mm:ss     06:30:07
24     yyyy'-'MM'-'dd HH':'mm':'ss'Z'     2006-08-22 06:30:07Z
25     dddd, dd MMMM yyyy HH:mm:ss     Tuesday, 22 August 2006 06:30:07
26     yyyy MMMM     2006 August
27     yyyy MMMM     2006 August


The patterns for DateTime.ToString ( 'd' ) :
0     MM/dd/yyyy     08/22/2006

The patterns for DateTime.ToString ( 'D' ) :
0     dddd, dd MMMM yyyy     Tuesday, 22 August 2006

The patterns for DateTime.ToString ( 'f' ) :
0     dddd, dd MMMM yyyy HH:mm     Tuesday, 22 August 2006 06:30
1     dddd, dd MMMM yyyy hh:mm     tt Tuesday, 22 August 2006 06:30 AM
2     dddd, dd MMMM yyyy H:mm     Tuesday, 22 August 2006 6:30
3     dddd, dd MMMM yyyy h:mm     tt Tuesday, 22 August 2006 6:30 AM

The patterns for DateTime.ToString ( 'F' ) :
0     dddd, dd MMMM yyyy HH:mm:ss     Tuesday, 22 August 2006 06:30:07

The patterns for DateTime.ToString ( 'g' ) :
0     MM/dd/yyyy HH:mm     08/22/2006 06:30
1     MM/dd/yyyy hh:mm     tt 08/22/2006 06:30 AM
2     MM/dd/yyyy H:mm     08/22/2006 6:30
3     MM/dd/yyyy h:mm tt     08/22/2006 6:30 AM

The patterns for DateTime.ToString ( 'G' ) :
0     MM/dd/yyyy HH:mm:ss     08/22/2006 06:30:07

The patterns for DateTime.ToString ( 'm' ) :
0     MMMM dd     August 22

The patterns for DateTime.ToString ( 'r' ) :
0     ddd, dd MMM yyyy HH':'mm':'ss 'GMT'     Tue, 22 Aug 2006 06:30:07 GMT

The patterns for DateTime.ToString ( 's' ) :
0     yyyy'-'MM'-'dd'T'HH':'mm':'ss     2006-08-22T06:30:07

The patterns for DateTime.ToString ( 'u' ) :
0     yyyy'-'MM'-'dd HH':'mm':'ss'Z'     2006-08-22 06:30:07Z

The patterns for DateTime.ToString ( 'U' ) :
0     dddd, dd MMMM yyyy HH:mm:ss     Tuesday, 22 August 2006 06:30:07

The patterns for DateTime.ToString ( 'y' ) :
0     yyyy MMMM 2006 August
Building a custom DateTime.ToString Patterns

The following details the meaning of each pattern character. Note the K and z character.

d     Represents the day of the month as a number from 1 through 31. A single-digit day is formatted without a leading zero
dd     Represents the day of the month as a number from 01 through 31. A single-digit day is formatted with a leading zero
ddd     Represents the abbreviated name of the day of the week (Mon, Tues, Wed etc)
dddd     Represents the full name of the day of the week (Monday, Tuesday etc)
h     12-hour clock hour (e.g. 7)
hh     12-hour clock, with a leading 0 (e.g. 07)
H     24-hour clock hour (e.g. 19)
HH     24-hour clock hour, with a leading 0 (e.g. 19)
m     Minutes
mm     Minutes with a leading zero
M     Month number
MM     Month number with leading zero
MMM     Abbreviated Month Name (e.g. Dec)
MMMM     Full month name (e.g. December)
s     Seconds
ss     Seconds with leading zero
t     Abbreviated AM / PM (e.g. A or P)
tt     AM / PM (e.g. AM or PM
y     Year, no leading zero (e.g. 2001 would be 1)
yy     Year, leadin zero (e.g. 2001 would be 01)
yyy     Year, (e.g. 2001 would be 2001)
yyyy     Year, (e.g. 2001 would be 2001)
K     Represents the time zone information of a date and time value (e.g. +05:00)
z     With DateTime values, represents the signed offset of the local operating system's time zone from Coordinated Universal Time (UTC), measured in hours. (e.g. +6)
zz     As z but with leadin zero (e.g. +06)
zzz     With DateTime values, represents the signed offset of the local operating system's time zone from UTC, measured in hours and minutes. (e.g. +06:00)
f     Represents the most significant digit of the seconds fraction; that is, it represents the tenths of a second in a date and time value.
ff     Represents the two most significant digits of the seconds fraction; that is, it represents the hundredths of a second in a date and time value.
fff     Represents the three most significant digits of the seconds fraction; that is, it represents the milliseconds in a date and time value.
ffff     Represents the four most significant digits of the seconds fraction; that is, it represents the ten thousandths of a second in a date and time value. While it is possible to display the ten thousandths of a second component of a time value, that value may not be meaningful. The precision of date and time values depends on the resolution of the system clock. On Windows NT 3.5 and later, and Windows Vista operating systems, the clock's resolution is approximately 10-15 milliseconds.
fffff     Represents the five most significant digits of the seconds fraction; that is, it represents the hundred thousandths of a second in a date and time value. While it is possible to display the hundred thousandths of a second component of a time value, that value may not be meaningful. The precision of date and time values depends on the resolution of the system clock. On Windows NT 3.5 and later, and Windows Vista operating systems, the clock's resolution is approximately 10-15 milliseconds.
ffffff     Represents the six most significant digits of the seconds fraction; that is, it represents the millionths of a second in a date and time value. While it is possible to display the millionths of a second component of a time value, that value may not be meaningful. The precision of date and time values depends on the resolution of the system clock. On Windows NT 3.5 and later, and Windows Vista operating systems, the clock's resolution is approximately 10-15 milliseconds.
fffffff     Represents the seven most significant digits of the seconds fraction; that is, it represents the ten millionths of a second in a date and time value. While it is possible to display the ten millionths of a second component of a time value, that value may not be meaningful. The precision of date and time values depends on the resolution of the system clock. On Windows NT 3.5 and later, and Windows Vista operating systems, the clock's resolution is approximately 10-15 milliseconds.
F     Represents the most significant digit of the seconds fraction; that is, it represents the tenths of a second in a date and time value. Nothing is displayed if the digit is zero.
:     Represents the time separator defined in the current DateTimeFormatInfo..::.TimeSeparator property. This separator is used to differentiate hours, minutes, and seconds.
/     Represents the date separator defined in the current DateTimeFormatInfo..::.DateSeparator property. This separator is used to differentiate years, months, and days.
"     Represents a quoted string (quotation mark). Displays the literal value of any string between two quotation marks ("). Your application should precede each quotation mark with an escape character (\).
'     Represents a quoted string (apostrophe). Displays the literal value of any string between two apostrophe (') characters.
%c     Represents the result associated with a c custom format specifier, when the custom date and time format string consists solely of that custom format specifier. That is, to use the d, f, F, h, m, s, t, y, z, H, or M custom format specifier by itself, the application should specify %d, %f, %F, %h, %m, %s, %t, %y, %z, %H, or %M. For more information about using a single format specifier, see Using Single Custom Format Specifiers.