Announcement

Collapse
No announcement yet.

Creating a report with datetime field search criteria results in erroneous SQL

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Creating a report with datetime field search criteria results in erroneous SQL

    Hi.

    I'm currently creating a report and using the query designer to enter fields, operators and values etc for the search criteria. All search criteria is working except with fields that are a "datetime" format. Selecting any operator "=" or a LIKE or BETWEEN produces an error because the resulting SQL that the query designer produces omits the values to be tested.

    For example, finding sales in August, I'd expect to be able to produce:
    Code:
    WHERE date_purchased BETWEEN '2016-08-01 00:00:00' AND '2016-08-31 23:59:59'
    but the query designer drops the values and ends up with:
    Code:
    WHERE date_purchased BETWEEN
    This happens consistently with any "datetime" format field. Am I missing something silly here or is this a bug with the report query designer?

    Hope you good Navicat folks can assist

    Paul.



  • #2
    In the report's Query Designer, please try to use this datetime format: dd/mm/yyyy hh:mm:ss
    e.g.
    '08/01/2016 00:00:00' AND '31/08/2016 23:59:59'

    Comment


    • #3
      Thank you very much - that will work.

      In case it helps anyone else, I created a VIEW to store the reformatted version of the date but couldn't pull the view into the query designer so I did the format conversion in the "Calcs" part of the query designer, creating a new alias field in the table that I could reference in the "Search" criteria:

      Code:
      date_format(`orders`.`date_purchased`,'%d/%m/%Y %H:%i:%s')

      Comment

      Working...
      X