2 Replies Latest reply: Apr 21, 2012 11:59 PM by dmshimself RSS

    Querying 2 different date objects in query builder




      I was wondering whether it is possible to create a query using the query builder that validates one date object against another date object. For example:-


      Indcident Due Date (custom date object) is less than indcient closed date (system landesk object).


      I'm assuming you would need to create a crystal report or something maybe that would be this instead of using the query builder.


      Any advice would be much appreciated.


      Kind Regards.


        • 1. Re: Querying 2 different date objects in query builder
          karenpeacock Employee



          Yes this can be done in Crystal.  Create a new formula which should be something like this:


          If {Due Date} < (Date Closed} then True else False


          Then you can use the Select Expert to only show records where the formula result is True / False as needed.  It's worth bearing in mind that your date fields actually store dates and times so if you wanted to compare dates you would need to convert them to just dates.  I think from memory you would just need to use ToDate, so for example ToDate({DueDate}).  Then you would need to make sure you also think about whether you want the formula to return True or False if the two dates are equal.


          Best wishes


          • 2. Re: Querying 2 different date objects in query builder
            dmshimself ITSMMVPGroup

            With query designer I'm afraid not.  I did raise an ER which I now cannot find which would allow the query design to use calculations.  If that were implemented you'd be able to do the sort of thing you had in mind.