DataStage-To list Fields that have Invalid Date

Question

I want to list the fields that have invalid date. Currently I only came out with below conditions to check date validity.

If 
IsValid('%dd-%mm-%yyyy',ln_source_1.A_Date) or
IsValid('%dd-%mm-%yyyy',ln_source_1.B_Date) or
IsValid('%dd-%mm-%yyyy',ln_source_1.C_Date) 
then 1 else 0

DATE TABLE

ID|A_Date|B_Date|C_Date

001|16-10-2017|16-31-2017|16-10-017

002|61-10-2017|01-01-2017|16-10-2017

Based on above example, B_Date and C_Date are not valid. So, is it possible to list the invalid date like below and how?

Status|Invalid_Date|

001|B_Date,C_Date|

002|A_Date|

Note: I have many date fields to check its validity and some of it can be null

Thanks

Answer 1

First of all the IsValid functions needs to be called like this:

IsValid("date", ln_source_1.A_Date, "%dd-%mm-%yyyy")

Your result example is unclear as your second column shows date but what you list is column names. Concatenating might be an option depending on how many "many date fiels" are.

You could go for a Stagevariable

svInvalid => set it to ""

svValid => set it to ""

And do the check like this (assuming all input and outfield are Varchar data types)

IF IsValid("date", ln_source_1.A_Date, "%dd-%mm-%yyyy")
THEN svValid : "A_DATE, "
ELSE svInvalid : "A_DATE, "

In the output columns assign svValid to a new VALID column and svInavalid to new INVALID column.