There are business use cases where dates have to be calculated based on certain cut off days. Especially this is important for account payables when invoices have to be paid based on certain criteria which involves cut off days configured for a specific vendor.
This can be achieved by using Oracle PL/SQL inbuilt date functions and by using them we can leverage the cut off days criteria to get the expected date values for paying the invoices.
In this article let’s look take a business use case for paying vendor on 10th of every month based on the invoice cutoff date.
Example:
Invoice date = 7/25/2020 then Payment date has to be = 8/10/2020
Invoice date = 7/26/2020 then Payment date has to be = 9/10/2020
Here we are using the cut off days = 26 and months forward = 1. But that will not resolve the issue when the invoice is on the exact cutoff date.
Hence we will use Oracle PL/SQL inbuilt date function and resolve this date logic. We will use case function to determine the current day and then add the months using ADD_MONTHS.
TRUNC (Date, ‘MM’) provides the 1st of the month and + 9 will give the 10th of the month.
SQL Query:
Output:
Top comments (0)