Dates in OQL not finding last day of the date range.
0
I want to limit a Report to a specific date range, e.g 1st June - 30th June 2016. I have tried both the following snippets of code and they both have the same issue: AND devs.ContractDate IN $datetest ... and devs.ContractDate >= RANGEBEGIN($datetest) AND devs.ContractDate <= RANGEEND($datetest) Data for 30th June 2016, is not included. The RangeEnd ($dateTest) is set to the start of day, so any data with dates equal to $dateTest are not extracted because the time part of devs.ContractDate is greater than the time part of RANGEEND($datetest). As OQL does not allow date calculations to add 1 day, there is not a way of making the OQL work, except to tell the users to set the end date as 1 day later (which is not user friendly). Does anyone know how to perform calculations within the Where clause on date / time?
asked
Nick Turpin
1 answers
0
You could select the day after in the date range and use
AND devs.ContractDate < RANGEEND($datetest)
If your data is datetime and RANGEEND($datetest) evaluates to 0:00 of the selected date then this should work and also explains why any datetime on a day is larger then (or equal to) 0:00 on that day