Date calculation incorrect in sql when +1 month entered

1160 pts.
Tags:
SQL
SQL Date Calculation
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question: 8  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    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).
    69,175 pointsBadges:
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • TomLiotta
    ...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
    125,585 pointsBadges:
    report
  • carlosdl
    "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 ;)
    69,175 pointsBadges:
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • JohnsonMumbai
    Thanks very much the function ADD_MONTHS() works fine. Johnson
    1,160 pointsBadges:
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • JohnsonMumbai
    [...] Date calculation incorrect in SQL when +1 month entered, which was asked by JohnsonMumbai and answered by [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following