JPQL query for comparing dates

My CUBA application is using SQL server at the backend. Now I have this query on SQL server that I want to run on CUBA on one of my datasources:

select * from process where DATEDIFF ( day , GETDATE() , DATEADD(DAY, 70, recepcionedDate)) < 10

Basically what this does is calculate the difference in days between today’s date and the receptionedDate field + 70 days, and return all rows on which this difference is bellow 10 days.

I know I can’t use these functions in JPQL, so I tried using the FUNCTION() operator in JPQL to run the native SQL server function, but it isn’t working.

Can anyone help?

Best regards,
Pedro

Hi,

there are JPQL macros which deals with date ranges:

Macros in JPQL - CUBA Platform. Developer’s Manual - that might what you are looking for:

select e from process e where @between(e.recepcionedDate, now-10, now, day)

Bye
Mario

1 Like

Hi Mario,

Thanks for your reply. The @between macro helped.

Regards,
Pedro

A post was split to a new topic: Error with JPQL macros