Topic
4 replies Latest Post - ‏2012-07-03T07:02:50Z by SystemAdmin
newcyh
newcyh
2 Posts
ACCEPTED ANSWER

Pinned topic Add_Months error when month > 99

‏2012-07-02T04:56:58Z |
Hi,
I have an Informix database and am getting an error when calling Add_Months with a month > 99. Example:
ADD_MONTHS(d_received, retention) where d_received is type Date and retention is type integer.
Getting the following error and I'm not sure of which value the error is referencing
Too many digits in the first field of datetime or interval.

I've searched high and low for how to fix this but am coming up blank. I've read the default precision is 2 for the INTERVAL but I can't figure out how to make this work for an integer > 99. Can anyone help me?
Thanks!
Updated on 2012-07-03T07:02:50Z at 2012-07-03T07:02:50Z by SystemAdmin
  • JJ@IBM
    JJ@IBM
    18 Posts
    ACCEPTED ANSWER

    Re: Add_Months error when month > 99

    ‏2012-07-02T06:50:31Z  in response to newcyh
    What version of INFORMIX are you using? Presumably pre 11.50.xC8 cos apparently in fc8 and higher the routine accepts more than 2 digits

    Perhaps you could write your own routine

    JJ

    "You are what you is"
  • SystemAdmin
    SystemAdmin
    1143 Posts
    ACCEPTED ANSWER

    Re: Add_Months error when month > 99

    ‏2012-07-02T07:01:15Z  in response to newcyh
    Hi,
    this is a known defect which was fixed in 11.50.xC8 and 11.70.xC2:
    'add_months does not accept value more than 99' (internal reference # for 11.50 family is idsdb00216173)
    Unfortunately there is not a workaround, so the only way is to write your own 'wrapper' procedure as suggested by JJ.
    Tomas
  • newcyh
    newcyh
    2 Posts
    ACCEPTED ANSWER

    Re: Add_Months error when month > 99

    ‏2012-07-02T23:36:06Z  in response to newcyh
    Hi,
    Thanks for the input, since ADD_MONTHS will not work, I'll look for another solution.
    • SystemAdmin
      SystemAdmin
      1143 Posts
      ACCEPTED ANSWER

      Re: Add_Months error when month > 99

      ‏2012-07-03T07:02:50Z  in response to newcyh
      Hi,
      here's a sample of how the wrapper procedure could look like:

      create function my_add_months (p_date date, nmonths int)
      returning date;
      define v_date date;
      let v_date = p_date;

      while (nmonths > 99)
      call add_months(v_date,99) returning v_date;
      let nmonths = nmonths - 99;
      end while

      call add_months(v_date,nmonths) returning v_date;
      return v_date;
      end function;

      Of course the 'nmonths' value can as well be negative, so you might need to modify/extend the sample above to handle that scenario as well.
      HTH, Tomas