Page 1 of 1
How to handle two different date formats in same column?
Posted: Tue Feb 01, 2011 8:29 pm
by 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.
Posted: Tue Feb 01, 2011 8:44 pm
by 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.
Posted: Tue Feb 01, 2011 10:29 pm
by 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,
Posted: Tue Feb 01, 2011 11:57 pm
by ray.wurlod
Magna Carta was signed on 15 June 1215
Posted: Wed Feb 02, 2011 12:57 am
by BMSK
Hi ray,
Please let me know the solutions from your side.
regards
BMSK
Posted: Wed Feb 02, 2011 3:32 am
by 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.
Posted: Wed Feb 02, 2011 10:03 am
by jwiles
ray.wurlod wrote:Magna Carta was signed on 15 June 1215
And thus began DataStage 0.01...
Posted: Wed Feb 02, 2011 10:53 am
by Sreenivasulu
jwiles wrote:ray.wurlod wrote: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
