eQL - format of DATECALC function

The syntax is as follows:

DATECALC(DateEffective,5,YEARS),

DATECALC(DateEffective,5,MONTHS),

DATECALC(DateEffective,5,DAYS)

The YEARS, DAYS and MONTHS options are case sensitive.

START WITH Document

SELECT

  Id,

  Copies.Id

WHERE

  Projects.Project.Status = 'F'

  AND DATECALC( DateEffective, 5, YEARS ) < $Today

so  above is the text of my eQL, it apperas to work ith e edit window but when I test the report I get an error message

that says " adding a value to a datetime caused an overflow"

I am using this in a file plan disposition rule

Hello Suep,

I’m not sure what is difference between edit mode and launching report normally, but from error message it looks like system reached the max date value (Dec, 31 9999) after adding 5 years to DateEffective.

Could you do a search on Documents with Date Effective Between 1/01/9993 and 31/12/9999 and if there are any please correct date for them and try running report again.

Regards

Gintautas

Also, check for null values in the date field, these may be causing unintended behavior. You can deal with nulls in a couple of ways:

* IFNULL(DateEffective, $Today) < . . . -- or whatever date you want to replace it for the comparison

* AND DateEffective IS NOT NULL  -- to eliminate objects that do not have a dateeffective set from the report

Should add - the report designer will fight you when you try to enter "IS NOT NULL", but it will work. I just paste it in. And, yes it is also case sensitive.