Topic
3 replies Latest Post - ‏2012-10-03T16:45:03Z by rsouto
rsouto
rsouto
8 Posts
ACCEPTED ANSWER

Pinned topic Error -1267 on EntityFunctions.AddMonths (Adding N months to a DATETIME)

‏2012-09-14T21:44:07Z |
The issue is known for quite some time: Informix will not treat invalid dates if they are invalid after the month is modified.

The current IDS .net provider is generating code the will cause errors when the month is changed for some reason and the date becomes out of range.

Is there any known workaround using LINQ and .Net?

I know there is a function ADD_MONTHS() that works just fine, but... how to call it using LINQ?
Better yet, is there a provider way for doing it?

More details below

References:
http://www.iiug.org/forums/classics/index.cgi/noframes/read/1742
http://www.tek-tips.com/viewthread.cfm?qid=1370547
https://groups.google.com/forum/?hl=en&fromgroups=#!topic/comp.databases.informix/zkjhMHFE4bM

The code sample that caused the problem:

// try to add 5 months to a column with DateTime
var entities = DbContext.SomeEntity.Where(u => u.dtentry.HasValue)
.Select(u => new {
Name = u.shortname,
DateEntry = u.dtentry,
SomeFutureDate = EntityFunctions.AddMonths(u.dtentry, 5)
});

This code is converted by the IBM IDS Provider to a query that would be:

SELECT
CAST(1 AS int) AS C1,
Extent1.shortname AS C2,
Extent1.dtentry AS dtentry,
Extent1.dtentry + CAST(5 AS int) UNITS MONTH AS C3 -- does not work
FROM
"informix".SomeEntity AS Extent1
WHERE
Extent1.dtentry IS NOT NULL

If the code generated used the ADD_MONTHS function would be:

SELECT
CAST(1 AS int) AS C1,
Extent1.shortname AS C2,
Extent1.dtentry AS dtentry,
ADD_MONTHS(Extent1.dtentry, 5) AS C3 -- DOES work
FROM
"informix".SomeEntity AS Extent1
WHERE
Extent1.dtentry IS NOT NULL

If you read this far, please, any help is appreciated. Thank you.

Regards,
Ricardo
Updated on 2012-10-03T16:45:03Z at 2012-10-03T16:45:03Z by rsouto
  • Arvind_Gupta
    Arvind_Gupta
    58 Posts
    ACCEPTED ANSWER

    Re: Error -1267 on EntityFunctions.AddMonths (Adding N months to a DATETIME)

    ‏2012-09-25T17:49:01Z  in response to rsouto
    Hi,

    We are looking into this and provide you feedback at the earliest.

    Thanks,
    Arvind
  • Arvind_Gupta
    Arvind_Gupta
    58 Posts
    ACCEPTED ANSWER

    Re: Error -1267 on EntityFunctions.AddMonths (Adding N months to a DATETIME)

    ‏2012-09-27T08:22:37Z  in response to rsouto
    Hi,

    This is a defect in our provider. I will open a defect. This will be addressed in future releases (depending on the priority for specific releases). If there is a need for this to be available much sooner, please contact IBM technical support and open a PMR with an appropriate severity, justification, etc.

    Thanks,
    Arvind
    • rsouto
      rsouto
      8 Posts
      ACCEPTED ANSWER

      Re: Error -1267 on EntityFunctions.AddMonths (Adding N months to a DATETIME)

      ‏2012-10-03T16:45:03Z  in response to Arvind_Gupta
      Thank you. For now, we´re trying to use AddDays instead.