Searching datetime using between drops edge values

Searching datetime using between drops edge values

Posted by: Tania.Johnson on 21 September 2017, 5:22 am EST

• 

  Tania.Johnson

  • Post Options:
  • Link
    Copy

  Posted 21 September 2017, 5:22 am EST

  We are using ActiveReports 10. We have a report that uses the following Where clause:
  
  WHERE i.dtmDateOfIncident BETWEEN ‘<%DateMin|||D%>’
  
  AND ‘<%DateMax|||D%>’

  The result set is missing 2 records, both that occur on the DateMax. If I were writing this in SQL I would search Between ‘2017-02-01 00:00:00.0000000’ AND ‘2017-02-28 23:59:00.0000000’.

  How can I address this issue in ActiveReports?

• 

  abdias.michael

  

  • Post Options:
  • Link
    Copy

  Posted 26 September 2017, 2:58 am EST

  Sorry, I could not reproduce the issue at my end with datetime fields. I have attached two images, one is the Sql query in the report and the second is the output. Kindly check. I have attached the report file as well, which is connected to the Orders table in the Northwind database.DateFilterBetween.zip

• 

  Tania.Johnson

  • Post Options:
  • Link
    Copy

  Posted 3 October 2017, 2:02 am EST

  Try changing one of your dates to 07/15/1996 23:50:00 PM.

• 

  abdias.michael

  

  • Post Options:
  • Link
    Copy

  Posted 3 October 2017, 6:12 pm EST

  This happens because if we do not provide the time in the where clause, it takes it as 00:00:00 by default, hence skips the record with 23:50:00 in the time part. Same behavior is observed in Sql as well (please see attached screen shot).
  
  I would suggest you to modify the sql query as below:

  select * from Orders where OrderDate between '7/4/1996 00:00:00' and '7/15/1996 23:59:59'
  ```[img]public\uploads\2502884bbe0d35b7eb70e01ab88c04541507101167655.png[/img]

• 

  Tania.Johnson

  • Post Options:
  • Link
    Copy

  Posted 4 October 2017, 8:36 am EST

  We understand why this is happening. I’ve explained that in the original problem statement. " If I were writing this in SQL I would search Between ‘2017-02-01 00:00:00.0000000’ AND ‘2017-02-28 23:59:00.0000000’."

  The problem is this is a report where the user selects dates from a date helper and we are using
  
  WHERE i.dtmDateOfIncident BETWEEN ‘<%DateMin|||D%>’ AND ‘<%DateMax|||D%>’ in the code. How do I modify the code behind the report to address this problem?

• 

  grapecity

  • Post Options:
  • Link
    Copy

  Posted 29 October 2017, 10:06 pm EST

  Hi,

  As mentioned, you need to modify the SQL string to use. In order to do that at run time, you may use PageReport API and for this you may refer to following article discussing the same:
  
  https://www.grapecity.com/en/blogs/modify-data-at-run-time

  Thanks,
  
  Ruchir Agarwal