This discussion is archived
7 Replies Latest reply: Oct 29, 2008 5:06 PM by 690399 RSS

Date Filters in Layouts

690399 Newbie
Currently Being Moderated
We want a layout for our insurance log screen that shows
all insurance policies that shows policies that have
expired 60 days ago or more. I tried to put in the
advanced filter:
<br
/>daysafter(   policy_expires
, today() ) < 60

which is
what I use in the InfoMaker report. I get an error. Is it
just not possible to make a layout filter like this?<br
/>
I'm using 11.1
  • 1. Date Filters in Layouts
    690399 Newbie
    Currently Being Moderated
    Not as pretty, but this seems to work in the layout
    filter:

    policy_expires
    <= (today()- 60)

    What
    this filter says is that the policy expiration date
    must be less than (before) or equal to 60 days ago.
  • 2. Date Filters in Layouts
    690399 Newbie
    Currently Being Moderated
    I tried to use that in the Layout filter and still get
    the error "There are problems with this filter. Please
    make any necessary corrections and check it again."
    I'm guessing that it does not like the today().<br
    />
    If I do the filter not using the
    "Advanced Filter..." option and set it for
    "Expiration" "Less than <" "Sep 30, 2008" and
    then change it to the Advanced Filter option I get


    policy_expires <
    to_date('09/30/2008',
    'MM/dd/yyyy')   <br
    />
    I try just modifying this, but have
    had no luck which leads me to believe that the system
    just can't handle today() in the filter. <br
    />
    Any thoughts?
  • 3. Date Filters in Layouts
    690399 Newbie
    Currently Being Moderated
    What is the database engine you are running on (MS
    SQL, Sybase, Oracle)?

    When you
    enter in to the Advanced Filter option, you are
    basically entering a SQL statement against the
    database, so the syntax you use is specific to that
    engine. For example, if using Sybase you can use a
    filter of days ( policy_expires, CURRENT TIMESTAMP )
    >= 30

    The 'days'
    function and CURRENT TIMESTAMP functions / expressions
    will vary if using MS SQL or Oracle.
  • 4. Date Filters in Layouts
    690399 Newbie
    Currently Being Moderated
    In our case we are using Sybase.
  • 5. Date Filters in Layouts
    690399 Newbie
    Currently Being Moderated
    We are using Oracle 9i. Any ideas what the appropriate
    filter would be for an oracle platform?
  • 6. Date Filters in Layouts
    690399 Newbie
    Currently Being Moderated
    Although I don't have a way to test this, I believe in
    Oracle you can use: SYSDATE - policy_expires >=
    30
  • 7. Date Filters in Layouts
    690399 Newbie
    Currently Being Moderated
    Brilliant! That worked. Thanks for the help.