How to handle two different date formats in same column?

[BMSK]

Hi all,

My input file contains date column.
In this date is coming in the format yyyymmdd and ddmmyyyy format.

But I want to do the date validation and convert into String.

for that I am using valid function and StringToDate( "DateColumn", "%yyyy%mm%dd") But how to handle ddmmyyyy fromat.

thanks
BMSK.

[mavrick21]

Can you filter them into two separate links based on a flag in each record? Then it would be easier to do the validation.

I don't think there is any other way to handle this scenario but I might be wrong.

[jwiles]

No need to split into separate links. This can be done within a single transformer.

Look at the 5th and 6th digits of the incoming date. If the value is not that of a valid month (01-12), then you are seeing the second format (ddmmyyyy) and can validate against that. Otherwise, you are seeing the first format (yyyymmdd) and can validate against it.

If date[5,2] > '12' then validate/convert ddmmyyyy else validate/convert yyyymmdd

Regards,

[ray.wurlod]

Magna Carta was signed on 15 June 1215

[BMSK]

Hi ray,

Please let me know the solutions from your side.

regards
BMSK

[ray.wurlod]

Until you can document a business rule that unambiguously can identify dates of different formats (such as how James suggested) there's nothing more I can add. 12150615 and 15061215 are both valid, but maybe your data don't deal with dates that old.

[jwiles]

Magna Carta was signed on 15 June 1215

And thus began DataStage 0.01...

[Sreenivasulu]

Magna Carta was signed on 15 June 1215

And thus began DataStage 0.01...

Datastage 0.01 to Datastage 8.5 took 800 years. Change in each version took 100 years