Date Formula Functions
Here are the formula functions that you can use to manipulate date data:
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.