Date difference between current date and service contract end date

[SrinivasaRaoK]

Dear Experts,

I am trying to retrieve service contracts that are set to expire within the next 30 days using an FSM Query, leveraging the standard date difference function. To achieve this, I am attempting to calculate the date difference between the current date and the service contract’s end date. However, this approach is not yielding the expected results.

I am currently using the following query:

SELECT serviceContract
FROM ServiceContract serviceContract
WHERE DATEDIFF(day,now(), serviceContract.endDate) = 30

Could you kindly assist me in determining the correct syntax to calculate the date difference between the current date and the service contract end date to identify contracts expiring in 30 days?

Best regards,
Srinivasa

[r_barabas · Accepted solution]

Hi Srinivasa

Try this to get a feel for the numbers:

SELECT DATEDIFF(day,now(), serviceContract.endDate)
FROM ServiceContract serviceContract;

Looking at this in my demo environments data, I think this is what you need:

SELECT serviceContract
FROM ServiceContract serviceContract
WHERE DATEDIFF(day, serviceContract.endDate, now()) = 30;

Notice how swapping the dates changes the result from a negative to a positive number.

Best regards

    Raphael Barabas

[SrinivasaRaoK · Accepted solution]

It worked for me. Thank you very much 🙂