EMail template for Analytic report that shows the number of days left until the user enddate

[DBorchers]

Hello,

we have created an Analytics report that sends an email 10 and 20 days before the user end date is reached.
Is it possible to display the remaining days until user deactivation (difference today - user enddate) in the email body?
Attempts to calculate the remaining days via groovy.time.TimeCategory have not worked so far.

[pruthvi_t]

Hi @DBorchers ,

You can try to make use of DATEDIFF() function in your query to calculate difference of days from today to end date.

You can use something like DATEDIFF(enddate,now()) as Remaining days in your SQL query.

ex: I used it to test for a user whose end date is 06-30-2023 and used DATEDIFF(enddate,now()) in SQL query and the days were shown as 115 in the result.

Thanks,

[Manu269]

@pruthvi_t I too have a similar ask from customer.

We need to display the remaining days until user deactivation (difference today - user enddate) in the email body?

[pruthvi_t]

@Manu269 , the datediff i mentioned was for query of the analytics. But to display the days on the email body, I’ll check on it and get back to you.

thanks,

[rushikeshvartak]

what is date format 

${new java.text.SimpleDateFormat('MMMM dd, yyyy').format(user?.endDate)}

[DBorchers]

Actually the ${user?.enddate} = 2023-03-18 00:00:00.0

When implementing ${new java.text.SimpleDateFormat('MMMM dd, yyyy').format(user?.endDate)} in my email body, i got only the following email: