Does DV has last_date function?

Question

Summary

Content

I need to create a calculation "Days left in MTD". The SQL is last_day(current_date) - current_date. However I am not able to find last_day function as a calendar function in DV. Is there a way to achieve this?

Gianni Ceresa · Answer

This function isn't standard SQL, so DVD doesn't have the function itself but you have all you need to calculate it ...

You have CURRENT_DATE and by using TIMESTAMPADD and some logic you easily get to the last day of the month.

The idea is to first get the 1st day of the next month based on the current date, by first subtracting the number of days of month from CURRENT_DATE (by using DAYOFMONTH), adding 1 month by using TIMESTAMPADD and then calculate the difference compared the CURRENT_DATE by using TIMESTAMPDIFF and subtract 1 (because you used the 1st of next month and not the last of current month).

Done !