Date calculation incorrect in sql when +1 month entered
Date calculation incorrect in sql when 1 month entered.
If we add 1 month to 28-Feb then it shows 28-Mar instead of 31-Mar. Similarly if I add 1 month to 30-Nov it should show 31-Dec instead of 30-Dec. SQL command run is SELECT CCDATE + 1 MONTHS FROM COGNOSMIS/xyzPF WHERE CCDATE = '2010-02-28'
Software/Hardware used:
os400 as400
If we add 1 month to 28-Feb then it shows 28-Mar instead of 31-Mar. Similarly if I add 1 month to 30-Nov it should show 31-Dec instead of 30-Dec. SQL command run is SELECT CCDATE + 1 MONTHS FROM COGNOSMIS/xyzPF WHERE CCDATE = '2010-02-28'
Software/Hardware used:
os400 as400
ASKED:
March 24, 2010 12:31 PM
UPDATED:
March 26, 2010 6:10 PM
Answer Wiki:
Since those are correct results, I have to assume that you are actually asking for a method of determining the end-of-month for NextMonth when you know the end-of-month for ThisMonth.
From the SQL Reference under the 'Datetime arithmetic in SQL' subtopic:<ul>
<li><b>Incrementing and decrementing dates:</b> The result of adding a duration to a date, or of subtracting a duration from a date, is itself a date. (For the purposes of this operation, a month denotes the equivalent of a calendar page. Adding months to a date, then, is like turning the pages of a calendar, starting with the page on which the date appears.) The result must fall between the dates January 1, 0001 and December 31, 9999 inclusive. If a duration of years is added or subtracted, only the year portion of the date is affected. The month is unchanged, as is the day unless the result would be February 29 of a non-leap-year. In this case, the day is changed to 28, an SQLSTATE of ’01506’ is assigned to the RETURNED_SQLSTATE condition area item in the SQL Diagnostics Area (or SQLWARN6 in the SQLCA is set to ’W’) to indicate the end-of-month adjustment.</li><li><i>Similarly, if a duration of months is added or subtracted, only months and, if necessary, years are affected. The day portion of the date is unchanged unless the result would be invalid (September 31, for example).</i> In this case, the day is set to the last day of the month, and SQLWARN6 in the SQLCA is set to ’W’ to indicate the end-of-month adjustment.</li>
</ul>
So, you might use <b>(((end_of_month + 1 day) + 1 month) - 1 day)</b> if you know that the "end_of_month" column is indeed a date representing the end of that month.
Tom
To see all answers submitted to the Answer Wiki: View Answer History.
Interesting.
As you said, Tom, those are correct results in the sense that according to that specific implementation those are the expected and documented results for that operation (and that’s why it’s important to read the product’s documentation instead of assuming…).
However, it is not really adding a month to the given date, but incrementing the month number, which is not the same.
I would also have expected a result of 31-Mar when adding a month to 28-Feb, but that is because I have worked with Oracle databases for many years, and that is the way the Oracle’s add_months function works (which btw, makes more sense to me).
Consider the different results — (’02.25.2010′ + 1 month), (’02.27.2010′ + 1 month) and (’02.28.2010′ + 1 month), and perhaps even (’02.29.2008′ + 1 month). What about (’02.27.2010′ + 6 months)?
How else can date arithmetic work?
Tom
…that is the way the Oracle’s add_months function works…
Yes, and the ADD_MONTS() and LAST_DAY() functions are available in DB2 on i5/OS V5R4 or later. The OP didn’t specify VRM and the question seemed to be about the specifics of adding 1 MONTH as a straight date duration to a date.
I possibly should have mentioned those in case they were available for the question. Thanks for the reminder.
Tom
“How else can date arithmetic work?”
Well, adding any number of months to 02.25.2010, 02.27.2010, or any other date that is not at the end of a month will not produce any change in the day part of the date even with the ADD_MONTHS function (unless the resulting date is invalid), but if at the end of the month, the day part could change.
For example:
add_months(’02.28.2010′ ,1) = ’03.31.2010′
add_months(’02.28.2010′,2) = ’04.30.2010′
The logic behind this is that if we are at the end of February and we add a month, we are adding March, which has 31 days. If we add one more month, we are adding April, which has 30 days, and so on.
But I guess this involves something more than arithmetic.
There are other implementations that produce the same results that JohnsonMumbai considered incorrect, for example the T-SQL’s DATEADD function.
“Thanks for the reminder.”
I didn’t know those functions were available in DB2 on i5/OS, so it was an unintentional reminder, but, you are welcome
I mostly agree. Yet we’re now talking about a specific function — ADD_MONTHS() — beyond direct addition of a date-duration. A function can be created by anyone to do whatever is desired. Seems reasonable that ADD_MONTHS() was added to SQL just for this purpose.
In this case, having the alternatives is the right situation to be in. JohnsonMumbai learns and can code as needed.
Tom
Thanks very much the function ADD_MONTHS() works fine.
Johnson
When using ADD_MONTHS(), be sure to review how the LAST_DAY() function can help guarantee that you’re using the “last day of the month”.
Tom