Answers.Body: <p>The issue has been reported in <a href="https://lab.civicrm.org/dev/report/issues/7" rel="nofollow noreferrer">https://lab.civicrm.org/dev/report/issues/7</a>.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-02-11T15:10:39.403Z
Answers.LastActivityDate: 2019-02-11T15:10:39.403Z
Answers.UserDisplayName: Mark Westergaard
Body: <p>Using the "Bookkeeping with extra fields" template, I noticed I was occasionally missing some transactions whenever I filtered on Transaction Date. I believe the issue is caused by the filter using only the date and not the time.</p>
  
  <p>For example, if today is February 10 and I filter Transaction Date on "Yesterday", the WHERE clause looks like:</p>
  
  <blockquote>
    <p>( financial_trxn.trxn_date >= '20190209') AND ( financial_trxn.trxn_date &lt;= '20190209' )</p>
  </blockquote>
  
  <p>Any transaction that isn't exactly at midnight is lost. Comparing to the Date Received filter, it uses date and time:</p>
  
  <blockquote>
    <p>( contribution.receive_date >= '20190209000000') AND ( contribution.receive_date &lt;= '20190209235959' )</p>
  </blockquote>
  
  <p>Perhaps I'm misinterpreting the use of the filters. I can run the same query directly, but with the time included for Transaction Date, and it seems to return the correct results.</p>
  
  <ul>
  <li>CiviCRM 5.10.0</li>
  <li>PHP 7.2.13</li>
  <li>MySQL 5.7.23</li>
  <li>Wordpress 5.0.3</li>
  </ul>
  
  <p>EDIT - originally this Q referenced ExtendedReports 3.5 but turns out it was a core report</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2019-02-10T20:11:58.383Z
Comments.Text: Suggest you open an issue at https://github.com/eileenmcnaughton/nz.co.fuzion.extendedreport if you are pretty certain about your findings
Comments.UserDisplayName: petednz - fuzion
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2019-02-10T20:20:19.567Z
Comments.Text: I just realized that the issue exists in the standard Bookkeeping Transactions report. I'm so used to using Eileen's extended version that I had forgotten the standard report.
Comments.UserDisplayName: Mark Westergaard
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2019-02-11T00:54:50.100Z
Comments.Text: in that case are you able to replicate it at dmaster.demo.civicrm.org?
Comments.UserDisplayName: petednz - fuzion
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2019-02-11T04:08:36.107Z
Comments.Text: Yes, same behavior on dmaster and wpmaster.
Comments.UserDisplayName: Mark Westergaard
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2019-02-11T04:43:03.587Z
Comments.Text: Can you pls update the question and ideally create an issue at https://lab.civicrm.org/groups/dev/-/issues
Comments.UserDisplayName: petednz - fuzion
Tags: <civireport>
Title: Transaction Date in Extended Report
ThreadText: # Transaction Date in Extended Report
  
  (Asked by: username_0 on 2019-02-10)
  
  Using the "Bookkeeping with extra fields" template, I noticed I was occasionally missing some transactions whenever I filtered on Transaction Date. I believe the issue is caused by the filter using only the date and not the time.
  
  For example, if today is February 10 and I filter Transaction Date on "Yesterday", the WHERE clause looks like:
  
  > ( financial\_trxn.trxn\_date >= '20190209') AND ( financial\_trxn.trxn\_date <= '20190209' )
  
  Any transaction that isn't exactly at midnight is lost. Comparing to the Date Received filter, it uses date and time:
  
  > ( contribution.receive\_date >= '20190209000000') AND ( contribution.receive\_date <= '20190209235959' )
  
  Perhaps I'm misinterpreting the use of the filters. I can run the same query directly, but with the time included for Transaction Date, and it seems to return the correct results.
  
  * CiviCRM 5.10.0
  * PHP 7.2.13
  * MySQL 5.7.23
  * Wordpress 5.0.3
  
  EDIT - originally this Q referenced ExtendedReports 3.5 but turns out it was a core report
  
  **Tags:** civireport
  
  > **Comments:**
  > **username_1**: Suggest you open an issue at https://github.com/eileenmcnaughton/nz.co.fuzion.extendedreport if you are pretty certain about your findings  
  > **username_0**: I just realized that the issue exists in the standard Bookkeeping Transactions report. I'm so used to using Eileen's extended version that I had forgotten the standard report.  
  > **username_1**: in that case are you able to replicate it at dmaster.demo.civicrm.org?  
  > **username_0**: Yes, same behavior on dmaster and wpmaster.  
  > **username_1**: Can you pls update the question and ideally create an issue at https://lab.civicrm.org/groups/dev/-/issues  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_0 on 2019-02-11. Score: 1
  
  The issue has been reported in <https://lab.civicrm.org/dev/report/issues/7>.
  
  
  ---
  
  