IDS Forum

Re: ADD_MONTHS function

Posted By: LLOYD SERRAO
Date: Monday, 2 July 2012, at 2:21 p.m.

In Response To: Re: ADD_MONTHS function (Jonathan Leffler)

IDS - 11.50.FC2
HP Unix 11.23

Both the below query works.
In my case when the retention period is > 99 i get the date out of range error.
I shall provide you more information shortly.

Lloyd

On Mon, Jul 2, 2012 at 10:26 AM, LLOYD SERRAO <serrao.lloyd@gmail.com>wrote:

> It still doesn’t work if my retention period is > 99.
> Is upgrade the only solution? We just upgraded to 11.50 few months back and
> again upgrading to 11.70 is not possible. Any other workaround?
>

Which version of IDS 11.50 are you using and on which platform?

This query (which is, I believe, both portable to your DB without any
changes and isomorphic with your 'retention period' problem) works
correctly for me on Linux (RHEL 5, x86/64) with IDS 11.50.FC8:

SELECT created + tabid UNITS MONTH FROM informix.systables WHERE tabid >=
100;

What do you get from it?

This query works correctly for me too:

SELECT MDY(6,28, 2011) + tabid UNITS MONTH FROM informix.systables WHERE
tabid >= 100;

So, you need to provide a 'working' example of your broken situation. That
is, you should respond with a simple table (1-3 columns) with simple data
(1-10) rows, and the query that fails when run against that table with that
data. WIth a reproduction in hand, we can try and work out what your
problem is. Without that, and without platform and version information
(note that every answer I've sent has had information about where I did my
testing; it is crucial information), there is nothing more I can do for you.

Note that I've already pointed out to you that if you have a date after the
28th of the month and you add an interval of months to that, you will
sometimes produce invalid dates which are reported as an error. For
example, adding one month to 30th January or 31st January is always an
error; adding it to 29th January is an error in a non-leap year; adding
three months to 31st January is an error, as is adding 5, 8 or 10 months.
All these additions also work when a multiple of 12 months is added to the
offsets; also, starting dates late in any other month can lead to the same
issue. Further, I've stated that the behaviour of the product has changed
in 11.70.xC5 (compared to xC4 and prior versions) so that in fact those
additions no longer produce an error. The fix may be available in the
11.50 latest version (xC10, or maybe an xC9 PID); you'd have to check on
that (with Tech Support).

Messages In This Thread

• ADD_MONTHS function
  Lloyd S -- Monday, 2 July 2012, at 10:00 a.m.
  • RE: ADD_MONTHS function
    Stuart Brooks -- Monday, 2 July 2012, at 10:41 a.m.
  • Re: ADD_MONTHS function
    Jonathan Leffler -- Monday, 2 July 2012, at 11:26 a.m.
    • Re: ADD_MONTHS function
      LLOYD SERRAO -- Monday, 2 July 2012, at 11:31 a.m.
      • Re: ADD_MONTHS function
        Jonathan Leffler -- Monday, 2 July 2012, at 11:44 a.m.
    • Re: ADD_MONTHS function
      LLOYD SERRAO -- Monday, 2 July 2012, at 11:36 a.m.
      • Re: ADD_MONTHS function
        Jonathan Leffler -- Monday, 2 July 2012, at 11:53 a.m.
        • Re: ADD_MONTHS function
          LLOYD SERRAO -- Monday, 2 July 2012, at 12:59 p.m.
          • Re: ADD_MONTHS function
            Jonathan Leffler -- Monday, 2 July 2012, at 1:59 p.m.
        • Re: ADD_MONTHS function
          LLOYD SERRAO -- Monday, 2 July 2012, at 1:26 p.m.
          • Re: ADD_MONTHS function
            Jonathan Leffler -- Monday, 2 July 2012, at 1:52 p.m.
            • Re: ADD_MONTHS function
              LLOYD SERRAO -- Monday, 2 July 2012, at 2:21 p.m.
              • Re: ADD_MONTHS function
                LLOYD SERRAO -- Monday, 2 July 2012, at 2:32 p.m.
                • Re: ADD_MONTHS function
                  Jonathan Leffler -- Monday, 2 July 2012, at 4:17 p.m.
                  • Re: ADD_MONTHS function
                    LLOYD SERRAO -- Tuesday, 3 July 2012, at 12:02 a.m.
  • Re: ADD_MONTHS function
    FRANK J. COMPUTER -- Monday, 2 July 2012, at 9:23 p.m.
    • Re: ADD_MONTHS function
      FRANK J. COMPUTER -- Monday, 2 July 2012, at 10:13 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.