DEV Community

Cover image for ADD_MONTHS and TRUNC for calculating dates in Oracle PL/SQL
Sharan Kumar Paratala Rajagopal
Sharan Kumar Paratala Rajagopal

Posted on

1

ADD_MONTHS and TRUNC for calculating dates in Oracle PL/SQL

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:

Alt Text

Output:

Alt Text

AWS GenAI LIVE image

Real challenges. Real solutions. Real talk.

From technical discussions to philosophical debates, AWS and AWS Partners examine the impact and evolution of gen AI.

Learn more

Top comments (0)

Billboard image

Create up to 10 Postgres Databases on Neon's free plan.

If you're starting a new project, Neon has got your databases covered. No credit cards. No trials. No getting in your way.

Try Neon for Free →

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay