Date Formula Functions

ADD_DAYS(date, n)

Adds n whole days to date.

Example:

ADD_DAYS ('30-DEC-1990' (date), 6)

It returns 5 JAN 1991.

ADD_MONTHS(date, n)

Adds n whole months to date.

ADD_YEARS(date, n)

Adds n whole years to date.

DAYS_BETWEEN(date1, date2)

Returns the number of days between date1 and date2. If date1 is later than date2, then the result is a positive number. If date1 is earlier than date2, then the result is a negative number.

Here's an example of this function:

DAYS_BETWEEN('1995/06/27 00:00:00' (date), '1995/07/03 00:00:00' (date))

It returns - 6.

Similarly;
DAYS_BETWEEN('2016/12/31 00:00:00'(DATE), '2017/01/01 00:00:00'(DATE))

It return -1.

GET_SYSDATE()

Returns the current system date value according to an internal notion of system date.

LAST_DAY(date)

Returns the last day of the month containing date.

LEAST(date, date [, date] ...)

Compares the operands and returns the earliest date.

MONTHS_BETWEEN(date1, date2)

Returns the number of months between date1 and date2. If date1 is later than date2, the result is a positive number. If date1 is earlier than date2, the result is a negative number. The return value has a numeric data type that can contain a fraction if the dates don't differ by a whole number of months.

NEW_TIME(date, zone1, zone2)

Returns the date and time in zone2 when the date and time in zone1 are date.

Here are the standard text strings that you can store in arguments zone1 and zone2:

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

NEXT_DAY(d, expr)

Returns the first date following d of the weekday named by expr.

You can specify the week day as a string that has a 3-letter prefix or a number. The prefix is case-independent.

Day	Prefix	Number
Monday	MON	1
Tuesday	TUE	2
Wednesday	WED	3
Thursday	THU	4
Friday	FRI	5
Saturday	SAT	6
Sunday	SUN	7

ROUND(date [,format])

Returns the result of rounding date according to format. The default format is DDD, which represents the nearest day.

TRUNC(date [,format])

Returns the result of truncating date according to format. The default format is DDD, which represents a whole day.