ADD_MONTHS () What if the second parameter is decimal?
We often use the ADD_MONTHS () function to obtain the date of a date after a few months. For example, we want to get the date after two months (January 1, April 05, 2013 ): chenZw> alter session set nls_date_format = 'yyyy-mm-dd ';
The session has been changed.
Used time: 00: 00: 00.00
ChenZw> select add_months (to_date ('201312', 'yyyymmdd'), 2) from dual;
ADD_MONTHS
----------
2013-06-05
Select 1 line.
Used time: 00: 00: 00.00
In the above example, we get the date of the second month after January 1, April 05, 2013, that is, January 1, June 05, 2013. This is the date that meets our expectation.
But there is a problem here. What if I want to get the date of April 05, 2013 months after that date (January 1, 1.2? Let's take a look: ChenZw> select add_months (to_date ('20140901', 'yyyymmdd'), 20130405) from dual;
ADD_MONTHS
----------
2013-05-05
Select 1 line.
Used time: 00: 00: 00.00
Why? This answer does not seem to be what I want. It should be 1.2 months later! Can you not be so rigorous! Should you have rounded me up? Let me know the number of today in the 1.9999999999999 month! ChenZw> select add_months (to_date ('201312', 'yyyymmdd'), 20130405) from dual;
ADD_MONTHS
----------
2013-05-05
Select 1 line.
Used time: 00: 00: 00.00
When I saw this answer, I was a little speechless. Fortunately, I didn't use it at work. Otherwise, I would be miserable, and the date may be wrong. Oracle, you can't help it.
In a confused mood, go to Oracle's official documentation:
ADD_MONTHSSyntax
Purpose
ADD_MONTHS returns the date plus integer months. A month is defined by the session parameter NLS_CALENDAR. the date argument can be a datetime value or any value that can be implicitly converted to DATE. the integer argument can be an integer or any value that can be implicitly converted to an integer. the return type is always DATE, regardless of the data type of date. if date is the last day of the month or if the resulting month has fewer days than the day component of date, then the result is the last day of the resulting month. otherwise, the result has the same day component as date.
Examples
The following example returns the month after the hire_date in the sample table employees: SELECT TO_CHAR (ADD_MONTHS (hire_date, 1), 'dd-MON-YYYY ') "Next month"
FROM employees
WHERE last_name = 'baer'; Next Month
-----------
07-JUL-2002
Now we understand that the original 1.9999999999999 is directly truncated to 1, and Oracle's so-called implicitly convert is the part after the decimal point.