Query beetwen two dates

Certified Senior Developer

Hi,
I kindly ask you for an opinion on a problem that I have been dragging along for some time.
I have a query in which I need to extract data between two dates: startDate and endDate.
The data on the db and CDT are in date and time format.
The range extremes are entered from the user interface in Date format, the same are the query rule inputs.

a!queryFilter(
   field: "timestamp",
   operator: "between",
   value: {
       todatetime(ri!startDate),todatetime(ri!endDate)
   }
),

But when I do, for example, todatetime (12/21/2020), I get 12/21/2020 1:00 AM GMT + 01: 00 (in my local time zone).

So I can't actually take dates from the beginning of startDate=00:00 AM to the end of endDate= 11:59 PM.

I am trying this solution:

a!queryFilter(
    field: "timestamp",
    operator: "between",
    value: {
      todatetime(concat(tostring(ri!startDate), " 00:00 AM")),
      todatetime(concat(tostring(ri!endDate), " 11:59 PM"))
    }
), 

It's that right? Are there any other solutions?

Regards, and thank you very much

  Discussion posts and replies are publicly visible

Parents Reply
  • 0
    Certified Lead Developer
    in reply to Paolo Scolamacchia

    A datetime stored in the DB is actually stored as a number that, as far as I remember, is accurate to the millisecond (and if that's wrong, it's at least accurate to the second).  Basically that means that when you want to query "everything for X date" for a column that stores a dateTime, it should always be sufficient to query between "Midnight of Date X" and "Midnight of Date X+1" (as opposed to bending over backwards to compare against "Date X at 23:59:59" specifically).

Children
No Data