How to calculate end of the month using DATEADD function in TIBCO Data Virtualization?

How to calculate end of the month using DATEADD function in TIBCO Data Virtualization?

book

Article ID: KB0076844

calendar_today

Updated On:

Products Versions
TIBCO Data Virtualization All supported versions

Description

This article explains how to calculate end of the month using DATEADD function in TIBCO Data Virtualization.

Note: 
TDV does not support End of month (EOMONTH) function.

Issue/Introduction

How to calculate end of the month using DATEADD function in TIBCO Data Virtualization?

Resolution

To find the end of the month when a date is entered, we will use DATEADD function to add a month to the given date, then DAY function is used to determine the number of days from the beginning of the month. By subtracting this from the date we just calculated (the one that is a month ahead), we can get the last date of the month.

Example for reference:
To find: End of month for October.
Input: A date from the month of October (10-04-2019)

select DATEADD('day',-day(dateadd('month',1,to_date('10-04-2019','mm-dd-yyyy'))),dateadd('month',1,to_date('10-04-2019','mm-dd-yyyy')))

Output: 2019-10-31

Powered by Wolken Software