7 Replies Latest reply: Oct 29, 2008 7:06 PM by 690399 RSS

    Date Filters in Layouts

    690399
      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
          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
            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
              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
                In our case we are using Sybase.
                • 5. Date Filters in Layouts
                  690399
                  We are using Oracle 9i. Any ideas what the appropriate
                  filter would be for an oracle platform?
                  • 6. Date Filters in Layouts
                    690399
                    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
                      Brilliant! That worked. Thanks for the help.