Date function in oracle

Date Functions: - These functions are works on date data types. In this date is in “DD-MON-YY” format. We can use “SYSDATE” to select current system date. We can pass any column with date datatype or sysdate as arguments in these functions.

To study these functions we take “product” table as example.

Selecting product table



They are divided as below:
1. ADD_MONTHS:- This function returns new date after adding month specified in argument.

Syntax
ADD_MONTHS(date,n)

In above syntax “date” is date argument and “n” is number of month we want to add in date specified as first arguments.
If “n” is negative then month will be subtracted from the date specified in argument.

Use of ADD_MONTHS function



2. MONTHS_BETWEEN:- This function returns months between the two dates we have passed as arguments.

Syntax
MONTHS_BETWEEN(date 1,date 2)

In above syntax “date 2” is date argument subtracted from “date 1” argument.
If “date 1” is greater than “date 2” result of month is negative otherwise positive and Zero if both date arguments are same.

Use of MONTHS_BETWEEN function



3. LAST_DAY:- This function returns last date of month specified by date.

Syntax
LAST_DAY(date)

In above syntax we get last date of month specified by “date” argument.

Use of LAST_DAY function



4. NEXT_DAY:- This function will returns the date of next week-day specified by day relative to date.

Syntax
NEXT_DAY(date, day)

In above syntax we get “date” is date argument and “day” is any week-day.

Use of NEXT_DAY function



5. ROUND:- This function will returns rounded date according to format.

Syntax
ROUND(date, format)

In above syntax we get “date” is date argument and “format” is any valid format combined with the abbreviation mention in below table.

Abbreviation	Specifies
MM	Numeric month (e.g., 07)
MON	Abbreviated month name (e.g., JUL)
MONTH	Full month name (e.g., JULY)
DD	Day of month (e.g., 24)
DY	Abbreviated name of day (e.g., FRI)
YYYY	4-digit year (e.g., 1998)
YY	Last 2 digits of the year (e.g., 98)
WW	No. of week in month
AM (or PM)	Meridian indicator
HH	Hour of day (1-12)
HH24	Hour of day (0-23)
MI	Minute (0-59)
SS	Second (0-59)

Use of ROUND function



6. TRUNC:- This function will returns truncated date according to format.

Syntax
TRUNC(date, format)

In above syntax we get “date” is date argument and “format” is any valid format combined with the abbreviation mention in above table.

Use of TRUNC function



7. NEW_TIME:- This function will returns date after converting from “zone 1” to “zone 2”.
In time zone is abbreviated as “XDT” or “XST” in which “DT” stands for daylight time and “ST” stands for standard time. “X” stands for many words as follows:

A – Atlantic
B – Berlin
C – Central
E – Eastern
H – Alaska-Hawaii
M – Mountain
N – Newfoundland
P – Pacific
Y – Yukon
GMT – Greenwich Mean Time

Syntax
NEW_TIME(date, zone 1, zone 2)

In above syntax we get “date” is date argument, “zone 1” and “zone 2” are zone name specified as per above format.

Use of NEW_TIME function



I hope this article will help you