Controlling the JPQL query output for past Dates

Support
#1

How can I setup my JPQL query so that it is pulling data for 7 days in the past?

Here is my current query(works for the current date):
select e from app$PeriodEnding e
where e.thedate >= CURRENT_DATE

I have tried things like:
select e from app$PeriodEnding e
where e.thedate >= CURRENT_DATE - 7

select e from app$PeriodEnding e
where e.thedate >= (CURRENT_DATE - 7 DAY)

select e from app$PeriodEnding e
where e.thedate >= (date() -7)

All of which cause an internal error.

#2

I was able to get my expected output by creating a step column in the database pushing the Date forward the 7 days.

It would still be good to know how to step the date directly in the datasource query.

#3

Hi Jay,

One of the solutions is to use @beetween macros in JPQL.

For example:

select e from app$PeriodEnding e where @between(e.thedate, now - 7, now + 35 000 , day)

where now + 35 000 - date far in the future

#4

Thank you Andrey!

#5

This actually causes an Internal Error and crashes the entire application.

#6

Hi Jay,

I think it’s a problem with my example: 35 000 - extra space in number. I changed query to

select e from app$PeriodEnding e where @between(e.thedate, now - 7, now + 35000 , day)

It’s worked

#7

I was able to get this to work. Thank you Andrey!