Dynamically Retrieving Dates in Oracle OTBI with TIMESTAMPADD
Working with dates dynamically is a common need in Oracle OTBI and BI Publisher reports, especially when analyzing historical data or periods relative to the current date. Oracle provides the powerful TIMESTAMPADD function in SQL to help generate dates on the fly—much needed for flexible, timely reporting.
What is TIMESTAMPADD?
The TIMESTAMPADD function allows you to perform date arithmetic by adding or subtracting specified intervals (days, months, years, etc.) from a timestamp or date. It’s invaluable for creating dynamic filters such as “last day of last month” or “the 17th of the prior month” without hardcoding static dates.
Practical Examples
Last Day of Last Month
This expression calculates the last day of the previous month relative to the current date:
TIMESTAMPADD( SQL_TSI_DAY , -1,
TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH(CURRENT_DATE) * -1 + 1,
CURRENT_DATE))
It works by:
Moving to the first day of the current month:
TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH(CURRENT_DATE) * -1 + 1, CURRENT_DATE)Then subtracting one day to get the last day of the previous month.
17th of Prior Month
To get the 17th day of the previous month:
TIMESTAMPADD(SQL_TSI_DAY, 16,
TIMESTAMPADD(SQL_TSI_MONTH, -1,
TIMESTAMPADD(SQL_TSI_DAY, DAYOFMONTH(CURRENT_DATE) * -1 + 1,
CURRENT_DATE)
)
)
This:
Finds the first day of the current month,
Goes back one month to the prior month,
Then adds 16 days to reach the 17th (as days are zero-based here).
17th of Current Month
Similarly, for the 17th of the current month:
TIMESTAMPADD(SQL_TSI_DAY, 16,
TIMESTAMPADD(SQL_TSI_DAY, DAYOFMONTH(CURRENT_DATE) * -1 + 1,
CURRENT_DATE)
)
Using TIMESTAMPADD in Filters: Payroll Example
For more complex filtering, like choosing the current or last month’s payroll period depending on the current date, you can embed TIMESTAMPADD within a CASE statement in your query:
SELECT "Payroll Period"."Period Name"
FROM "Payroll - Payroll Balances Real Time"
WHERE CASE
WHEN DAYOFMONTH(CURRENT_DATE) < 17 THEN
TIMESTAMPADD(SQL_TSI_DAY, 16,
TIMESTAMPADD(SQL_TSI_MONTH, -1,
TIMESTAMPADD(SQL_TSI_DAY, DAYOFMONTH(CURRENT_DATE) * -1 + 1,
CURRENT_DATE)
)
)
ELSE
TIMESTAMPADD(SQL_TSI_DAY, 16,
TIMESTAMPADD(SQL_TSI_DAY, DAYOFMONTH(CURRENT_DATE) * -1 + 1,
CURRENT_DATE)
)
END BETWEEN "Payroll Period"."Start Date" AND "Payroll Period"."End Date"
FETCH FIRST 1 ROWS ONLY
This dynamic condition checks if the current date is before the 17th day of the month:
If yes, it selects the 17th of the prior month’s payroll period.
Otherwise, it selects the 17th of the current payroll period.
Why Use TIMESTAMPADD?
Flexibility: Users don’t need to change reports every month.
Accuracy: Dates are computed exactly relative to current system time.
Maintainability: One dynamic formula handles all date range changes.
Summary
The TIMESTAMPADD function is an indispensable tool for Oracle OTBI report developers working with dynamic date calculations. Whether you’re building monthly reports, analyzing payroll periods, or creating rolling windows, mastering TIMESTAMPADD empowers you with flexible, accurate, and maintainable date logic.
Need some assistance?
Ready to bring clarity to customer, supplier and employee communications? With BI Publisher, we specialise in customising customer, supplier, and employee-facing documents such as invoices, purchase orders, payslips, and statements to meet your exact branding, compliance, and operational needs. From layout design to data logic enhancements, we help you deliver clear, consistent, and impactful outputs across your organization. Contact us today to discover how our expertise in BI Publisher can streamline your document processes and elevate your business communications.
