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