IDS Forum

Re: ADD_MONTHS function

Posted By: Jonathan Leffler
Date: Monday, 2 July 2012, at 4:17 p.m.

In Response To: Re: ADD_MONTHS function (LLOYD SERRAO)

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

> To add further -
>
> The query that does not work was
> ADD_MONTHS(a.d_received , e.n_retention_period) (but only if
> e.n_retention_period is > 99)
> I also tried
> ADD_MONTHS(MDY(MONTH(a.d_received),DAY(a.d_received),YEAR(a.d_received)),
> e.n_retention_period)
> which is what I thought you meant to do. I get same error for both
> “(-1261) Too many digits in the first field of datetime or interval.”
>

I tried this to simulate your scenario:

CREATE TABLE test_add_months
(

d_received DATE NOT NULL,

n_retention_period INTEGER NOT NULL
);
INSERT INTO test_add_months VALUES(MDY(6,28,2012), 100);
INSERT INTO test_add_months VALUES(MDY(6,28,2011), 999);
INSERT INTO test_add_months VALUES(MDY(6,28,2011), 9999);
SELECT d_received, n_retention_period,

ADD_MONTHS(d_received, n_retention_period) AS deletion_date
FROM test_add_months;

2012-06-28 100 2020-10-28
2011-06-28 999 2094-09-28
2011-06-28 9999 2844-09-28

That looks best in fixed-width font. This test was on RHEL 5 x86/64 with
IDS 11.70.FC4.

> However this below query works to some extent, still testing though -
>
> (MDY(MONTH(a.d_received),DAY(a.d_received),YEAR(a.d_received))
> + e.n_retention_period UNITS MONTH <= Today
>

You shouldn't need to reconstruct the date like that, but with the same
data as above:

SELECT d_received, n_retention_period,

MDY(MONTH(d_received),DAY(d_received),YEAR(d_received)) AS
rebuilt_date,

MDY(MONTH(d_received),DAY(d_received),YEAR(d_received)) +
n_retention_period UNITS MONTH
FROM test_add_months;

2012-06-28 100 2012-06-28 2020-10-28
2011-06-28 999 2011-06-28 2094-09-28
2011-06-28 9999 2011-06-28 2844-09-28

One possible (but not plausible) difference between what you're using and
what my reproduction is using is 'two tables' vs 'one table'. Your code
has table aliases 'a' and 'e' in use; mine has no need of them. I don't
think this will actually account for the difference, but it is one of the
differences left. Another possibility is that the type of your retention
period column is different from the INTEGER type I'm using; this is why
your minimal reproduction is crucial. (You'll need it anyway if you get
around to reporting a bug to IBM/Informix Technical Support.) Another
possible difference is in the setting of DBDATE, but that should not affect
the calculations.

IDS - 11.50.FC2
> HP Unix 11.23
>

Thanks for including the version information. It's a fairly old version of
11.50 (circa August 2008, about 4 months after FC1 was released); the
current version number in the 11.50 series is FC9W2 (March 2012). If the
worst comes to the worst, I can dig out the old version you're using, but I
hope it won't come to that.

Both the below query works.
>

That's useful to know. That probably means that the server is working as I
expect, and there is some detail in your setup that isn't being simulated
accurately in my attempted reproductions.

> 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.
>

--
Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."

--f46d04016b1f6d7b7704c3de7d65

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.