After reading this chapter, you will learn the following:
- Months_ Order of two dates between
- NEXT_ Notes on day return date
--ADD_ Months (D, n), adding the specified number of months to a certain date D, returns the new date after calculation. D is the date, n is the number of months to add (n can be negative) SELECT ADD_MONTHS(SYSDATE, 1), ADD_MONTHS(DATE'2019-08-30', -6), ADD_MONTHS(DATE'2020-08-30', -6) FROM DUAL; --LAST_ Day (d), returns the last day of the month of the specified date SELECT LAST_DAY(SYSDATE),LAST_DAY(DATE'2019-08-30') FROM DUAL; -- MONTHS_ Between (date1, date2), used to calculate the number of months between date1 and date2 SELECT MONTHS_BETWEEN(TO_DATE('2014-3-21', 'yyyy-mm-dd'), TO_DATE('2014-1-10', 'yyyy-mm-dd')) mon_diff FROM DUAL; --Note: the date can be added or subtracted by numbers, which means the number of days; the date minus the date means the number of days; the date cannot be added SELECT DATE'2019-08-30'+6, DATE'2019-08-30'-6, DATE'2019-08-30'-DATE'2019-08-16' FROM DUAL;
Note: months_ Between’sThe first date is greater than the second dateOtherwiseReturns a negative number。
Next_ Day: If today is a Saturday, if you want to query the next Saturday, return the Saturday of next week; if you want to query the next Sunday, the return value is the Sunday of this week.
--NEXT_ Day (D, date2 [Mon, Tues,......]), returns the next (specified) day of the specified date SELECT NEXT_DAY(DATE'2019-08-30','friday') FROM DUAL; SELECT NEXT_DAY(SYSDATE,1) FROM DUAL;
Attention to forced conversion of date: dates can only be subtracted. Return the days of both.
The function is equivalent to months_ Between, but not add!
--Note: the date can be added or subtracted by numbers, which means the number of days; the date minus the date means the number of days; the date cannot be added SELECT DATE'2019-08-30'+6, DATE'2019-08-30'-6, DATE'2019-08-30'-DATE'2019-08-16' ----Date '2019-08-30' + date '2019-08-16' not allowed FROM DUAL;
Published on 05-30