We are doing a project for migrating data transformations from On-Prem to EMS.

SAP HANA is used for On-Prem transformations.

We have a blocker where a function -> DATS_IS_VALID() is used in the transformations in On-Prem.

The purpose of this function is to check if a particular date value (in the format YYYYMMDD) is valid or invalid.

For example: 20211130 is valid (Nov 30 2021) whereas 20211131 is invalid (coz November doesn’t have 31 days)

Now there is no direct syntax in Vertica to perform this operation as per our research.

Since we have many transformations using this check, we would need your help in finding out a better way to solve this problem.

Our conditions are mostly like, if the date is valid then take that specific date field and if the date is invalid then treat it as NULL.

Invalid dates are present in Production data as well as there are some manual invoicing being done hence we cannot remove or ignore this check.