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")) } ),
Regards, and thank you very much
Discussion posts and replies are publicly visible
You can't use the todatetime() function with multiple parameters or it will assume you want a list of datetime. You should look at the datetime() function which will let you create your own datetime more easily. As another approach, you can look into using two a!queryFilters with the greater than or less than operators
Thanks Danny,but I'm not sure I understand.The second queryFilter does not give errors but it seems to me too complicated.The point is that, taking for example the second extreme of the query,if the user enters e.g. Nov 15 2020,the query should have as endDate: Nov 15 2020 11:59 PM, to extract all data with timestamp of the day.but the simple todatetime (--15-Nov-2020--) returns15 Nov 2020 1:00 AM GMT+1 so all data after 1:00 AM will be excluded from the query.