[SQL common functions] Part 5 date functions

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