Date Functions
SYSDATE : This function returns date and time. SYSDATE returns time and date from the servers operating system
CURRENT_DATE : This function returns the date from the user's session time zone.
CURRENT_TIMESTAMP : This function returns the date and time from the user's session time zone.
SESSIONTIMEZONE : This function returns session time zone.
Arithmetic with Dates:
Operation Result Description
date + number Date Adds a number of days to a date
date – number Date Subtracts a number of days from a date
date – date Number of days Subtracts one date from another
date + number/24 Date Adds a number of hours to a date
• MONTHS_BETWEEN(date1, date2): Finds 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): Adds n number of calendar months to date. The value of n
must be an integer and can be negative.
• NEXT_DAY(date, 'char'): 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): Finds the date of the last day of the month that contains date
The preceding list is a subset of the available date functions. ROUND and TRUNC number
functions can also be used to manipulate the date values as shown below:
• ROUND(date[,'fmt']): Returns date rounded to the unit that is specified by the
format model fmt. If the format model fmt is omitted, date is rounded to the nearest day.
• TRUNC(date[, 'fmt']): Returns date with the time portion of the day truncated to
the unit that is specified by the format model fmt. If the format model fmt is omitted,
date is truncated to the nearest day.
