Database Reference
In-Depth Information
REMAINDER(n, m)
. This function is a remainder or modulus
function specifically for binary floating-point numbers.
The next section covers date functions.
9.2.3
Date Functions
ADD_MONTHS(date, months)
,
NEXT_DAY(date, weekday)
,
LAST_DAY(date)
,
and
MONTHS_BETWEEN(date, date)
.
ADD_MONTHS will add or subtract a number of months to a date
where differences in the number of days in months default to the last
day in the resulting month. NEXT_DAY finds the first day from the
date specified for the day of the week in the string contained in the
second parameter. LAST_DAY finds the last day in the month.
MONTHS_BETWEEN will return the number of months between
two dates.
ADD_MONTHS('27-AUG-02',4) = 27-DEC-02
NEXT_DAY('27-AUG-02','MONDAY') = 02-SEP-02
LAST_DAY('27-AUG-02') = 31-AUG-02
MONTHS_BETWEEN('27-AUG-02','01-JAN-02')
= 7.83870968 months
Note:
In the examples listed, note how dates are listed as strings and a
TO_DATE conversion function is not required. This is because DD-
MON-YY is the default date format and there is an implicit string-to-date
datatype conversion. The default date format can be altered. Datatypes are
covered in Chapter 16.
SYSDATE
,
CURRENT_DATE
,
CURRENT_TIMESTAMP(preci-
sion)
,
LOCALTIMESTAMP(precision)
,
and
SYSTIMESTAMP
.
SYSDATE and CURRENT_DATE find the system date setting on
the database server where CURRENT_DATE is timezone sensitive.
The other functions all provide different variations on timestamps.
SQL> SELECT SYSDATE FROM DUAL;
SYSDATE
--------------
23-JAN-04
SQL> SELECT SYSTIMESTAMP FROM DUAL;
SYSTIMESTAMP
------------------------
23-JAN-04 01.03.20.661000 AM -05:00
