I have Invalid dates in my Input file such as
00/00/0000
02/29/2009(Invalid date,not a leap year)
I am using the below logic to convert the date
DateToString(StringToDate(Inputcolum,'%mm/%dd/%yyyy'),'%yyyy%mm%dd')
This logic works fine for Valid dates.where ever there are invalid dates the output is given as **********
The datatype for source and target both are varchar. Can anyone suggest me how I can convert these invalid dates to null.
Thanks
Hope
Removing Invalid date from the source file
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Create a reject link on your Sequential File stage that reads the file and specify Date as the data type for this column.
Process the reject stream to generate null to replace this field, and funnel it back into mainstream processing.
Process the reject stream to generate null to replace this field, and funnel it back into mainstream processing.
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.
Don't specify it as a date in the input file, run it through a validation defaulting any invalid dates to a constant (generally 1111/11/11 or 9999/12/31).Hope wrote:When I specify the column datatype as Date.I am getting the following error.I cannot read the file
Data string '02/07/2010' does not match format '%yyyy-%mm-%dd': the value for tag %yyyy has fewer characters than expected.
You error above is obvious as to why... your project has defined the date format to be yyyy-mm-dd which you aren't providing in your input. You need to do one of
- have input changed to the date format specified
- change the format in a transformer
- redefine the project expectation...
-
- Premium Member
- Posts: 5
- Joined: Fri Oct 12, 2007 2:18 pm
Re: Removing Invalid date from the source file
PX:: There is function ""IsValid"" under Type Conversion Functions to check if its valid date or not.
If you can use basic transformer or basic routine......
---- Here Arg1 is your InputColumn.
If Arg1 = Oconv(Iconv(Arg1,"D/MDY[2,2,4]"), "D/MDY[2,2,4]")
Then
x = Arg1[7,4]:Arg1[1,2]:Arg1[4,2]
End
Else
x = @null
End
Ans = x
If you can use basic transformer or basic routine......
---- Here Arg1 is your InputColumn.
If Arg1 = Oconv(Iconv(Arg1,"D/MDY[2,2,4]"), "D/MDY[2,2,4]")
Then
x = Arg1[7,4]:Arg1[1,2]:Arg1[4,2]
End
Else
x = @null
End
Ans = x
-
- Participant
- Posts: 3
- Joined: Tue Feb 07, 2006 6:56 am