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.
How to handle two different date formats in same column?
Moderators: chulett, rschirm, roy
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,
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,
- james wiles
All generalizations are false, including this one - Mark Twain.
All generalizations are false, including this one - Mark Twain.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 892
- Joined: Thu Oct 16, 2003 5:18 am