JPQL query for comparing dates

Support
#1

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

#2

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
#3

Hi Mario,

Thanks for your reply. The @between macro helped.

Regards,
Pedro

#4

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

#5