ADD_MONTHS function

All,IDS 11.5 FC2HP UNIX 11I have a query which makes uses of ADD_MONTHS function ADD_MONTHS(a.d_received , e.n_retention_period) but the e.retention period only allows up to 99 and I need it to be greater than that. How can we make the retention value > 99.  If the number of months in retention value is greater than 99 i get the below error. (-1261) Too many digits in the first field of datetime or interval.] Appreciate your help. Thanks in advance.Lloyd

Hi Lloyd, Try using INTERVAL instead of ADD_MONTHS. I assume ADD_MONTHS is restricting you to 2 digits precision whereas with INTERVAL you can qualify it to 1 month intervals and multiply it by the number of months you want. For example: SELECT a.d_received + INTERVAL(1) MONTH TO MONTH * e.n_retention_period Hope that helps, Stuart --- Ardenta Ltd is a company registered in England and Wales. Registered number: 4181041. Registered office: Saxon House, Downside, Sunbury on Thames, Middlesex, TW16 6RT. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Lloyd S Sent: 02 July 2012 15:01 To: ids@iiug.org Subject: ADD_MONTHS function [27503] All,IDS 11.5 FC2HP UNIX 11I have a query which makes uses of ADD_MONTHS function ADD_MONTHS(a.d_received , e.n_retention_period) but the e.retention period only allows up to 99 and I need it to be greater than that. How can we make the retention value > 99.  If the number of months in retention value is greater than 99 i get the below error. (-1261) Too many digits in the first field of datetime or interval.] Appreciate your help. Thanks in advance.Lloyd ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

On Mon, Jul 2, 2012 at 7:00 AM, Lloyd S <lloyd_s@rediffmail.com> wrote: > All,IDS 11.5 FC2HP UNIX 11I have a query which makes uses of ADD_MONTHS > function > > ADD_MONTHS(a.d_received , > e.n_retention_period) but the e.retention period only allows up to 99 and I > need it to be greater than that. How can we make the > retention value larger than 99. If the number of months in retention value > is > greater than 99, I get the below error. > > (-1261) > Too many digits in the first field of datetime or interval.] > What is the type of `e.n_retention_period'? If it is an INTERVAL MONTH TO MONTH, then you need to change its type to INTERVAL MONTH(6) TO MONTH (which should be good for retention periods up to about 8000 years (you can change the 6 to 4 if 80 years is long enough for you). You can safely write: select add_months(mdy(6,30,2012), 9), add_months(mdy(6,30,2012), 99), add_months(mdy(6,30,2012), 999), add_months(mdy(6,30,2012), 9999) from sysmaster:informix.sysdual; This yields: 2013-03-30 2020-09-30 2095-09-30 2845-09-30 (I run with DBDATE=Y4MD-; the example code using MDY() works regardless of the setting of your locale and DBDATE. Testing on IDS 11.70.FC2 on Mac OS X 10.7.4 - yes, I should upgrade.) -- 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." --f46d04088ef5d3067104c3da6b9a

@Stuart, This is like my original query ((a.d_received + e.n_retention_period UNITS MONTH ) and gives me an error: “The result of a datetime computation is out of range” Which I found to be caused by leap year dates. That is why I changed to Add_months but now I can’t get that to use precision >2 Lloyd

Jonathan, a.d_received is date and e.n_retention_period is integer. Will this solution work?

On Mon, Jul 2, 2012 at 8:31 AM, LLOYD SERRAO <serrao.lloyd@gmail.com> wrote: > This is like my original query ((a.d_received + e.n_retention_period UNITS > MONTH ) and gives me an error: > “The result of a datetime computation is out of range” > Which I found to be caused by leap year dates. That is why I changed to > Add_months but now I can’t get that to use precision >2 > select mdy(6,30,2012) + 100 units month from sysmaster:informix.sysdual; 2020-10-30 Again, this is 11.70.FC2 on Mac OS X 10.7.4. However, if you are getting the error in 11.50, then it appears that an upgrade to 11.70 should sort that problem out. (And no, I don't normally write that sysdual incantation -- I create a table per database called 'dual' so I don't have to write out so much. However, the notation shown is pretty much guaranteed to work regardless of the (supported) version of IDS that you're using, so...) -- 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." --e0cb4efe2ae06c459e04c3da93e5

Don't remove all the context for a question -- it makes the follow-up meaningless to those coming later. Yes, trim old message; no, do not delete all of it! On Mon, Jul 2, 2012 at 8:36 AM, LLOYD SERRAO <serrao.lloyd@gmail.com> wrote: > Jonathan, > > a.d_received is date and e.n_retention_period is integer. Will this > solution > work? > I showed: select add_months(mdy(6,30,2012), 9), add_months(mdy(6,30,2012), 99), add_months(mdy(6,30,2012), 999), add_months(mdy(6,30,2012), 9999) from sysmaster:informix.sysdual; This worked. MDY() produces a DATE (not a DATETIME). And 9, 99, 999, and 9999 all look more like integers than intervals. I have a 'table of elements' in my database. I ran this query and got error "SQL -1267: The result of a datetime computation is out of range": select mdy(6,30,2012) + atomic_number units month from elements; It ground to a halt when processing Oxygen (element 8) because there is no date 2013-02-30. Even that is fixed in 11.70.FC5 (it would produce 2013-02-28 since 2013 is not a leap year; if the start date was 2011-06-30, Oxygen would give 2012-02-29). Change the query to: select mdy(6,28,2012) + atomic_number units month from elements; and it generates dates from 2012-07-28 to 2022-04-28. As before, testing IDS 11.70.FC2 on Mac OS X 10.7.4. -- 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." --e0cb4efe2e62394fac04c3dab1cc

Jonathan, Thanks, i think this should solve my problem. Will keep you posted. Lloyd

Jonathan, 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? Lloyd

Please pay attention to etiquette rules. 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). -- 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." --e0cb4efe34ec90f67104c3dc77e8

On Mon, Jul 2, 2012 at 9:59 AM, LLOYD SERRAO <serrao.lloyd@gmail.com> wrote: > Jonathan, > > Thanks, i think this should solve my problem. > I'm glad it helped. Which of half a dozen or so messages was it that provided the help? > Will keep you posted. > There was a later email from you saying you were still having problems. Does this mean that whatever this was a response to didn't help, or ... It is impossible to carry on a useful conversation when there's no indication of what any given response is a response to. Enough said on the subject for now; I hope I've made my point about including enough context clear. If you demonstrate that you can't learn, though, you will not be getting further responses from me. -- 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." --e0cb4efe34ec20508204c3dc8d90

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

Jonathan, 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.” 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 Will keep you posted. Thanks and appreciate your prompt help. Lloyd 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).

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

If you're looking for a reliable results, I suggest you do not use ADD_MONTHS() since the number of days in a month can vary and return an invalid date. One workaround I've been using is to define each month as having 30 days, so create your own function which multiplies the number of months by 30 and add that result to the date.

.. another alternative would be to create a fact table containing the number of days in each month for a specified range of years, do date arithmetic to convert retention_period to exact number of days (date_received + exact number of days).

Jonathan, Thanks for your help, the below query worked fine - (MDY(MONTH(a.d_received),DAY(a.d_received),YEAR(a.d_received)) + e.n_retention_period UNITS MONTH <= Today) Also found out that its 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) Many thanks for your help. Lloyd 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."