Date match format error
Moderators: chulett, rschirm, roy
Date match format error
Hi,
i am reading CSV file but getting the below error.
APT_CombinedOperatorController,0: Data string ' ' does not match format '%mm/%dd/%yyyy': an integer was expected to match tag %mm.
the source column is Complete_DATE VARCHAR(10)
SEQFILE..............xfm.................DB2 table.
in tranformer i have also tried with function
If IsNull(select_stats.Complete_DATE) then SetNull() else StringToDate(select_stats.Complete_DATE,'%mm/%dd/%yyyy')
The date in source file is given below the last column is date column
20451,1,2006/07/28
30451,10,2006/07/28
50451,15,2006/08/16
60451,45,
70451,49,
80451,53,
20451,55,
30451,67,
00451,78,
70451,86,
40451,98,
40451,400,
210906,M01,2005/10/31
210906,1,2005/10/31
210906,10,2005/10/31
Can you please provide the solution to get rid of this.
i am reading CSV file but getting the below error.
APT_CombinedOperatorController,0: Data string ' ' does not match format '%mm/%dd/%yyyy': an integer was expected to match tag %mm.
the source column is Complete_DATE VARCHAR(10)
SEQFILE..............xfm.................DB2 table.
in tranformer i have also tried with function
If IsNull(select_stats.Complete_DATE) then SetNull() else StringToDate(select_stats.Complete_DATE,'%mm/%dd/%yyyy')
The date in source file is given below the last column is date column
20451,1,2006/07/28
30451,10,2006/07/28
50451,15,2006/08/16
60451,45,
70451,49,
80451,53,
20451,55,
30451,67,
00451,78,
70451,86,
40451,98,
40451,400,
210906,M01,2005/10/31
210906,1,2005/10/31
210906,10,2005/10/31
Can you please provide the solution to get rid of this.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
And, sure enough, the dates in the data don't match the specified format string. The solution obtains immediately from the foregoing - you have to make them match (probably by providing a correct format string). You may also have to handle the empty (null?) date fields.
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.
sorry my mistake date format is coming like 07/12/2007.Still same error coming.
given below how is coming in CSV file
20451,1,07/28/2006
30451,1,07/28/2006
80451,10,07/28/2006
70451,15,08/16/2006
60451,45,
50451,49,
40451,53,
90451,55,
00451,67,
40451,78,
40451,86,
40451,98,
40451,400,
210906,M01,10/31/2005
210906,1,10/31/2005
210906,10,10/31/2005
given below how is coming in CSV file
20451,1,07/28/2006
30451,1,07/28/2006
80451,10,07/28/2006
70451,15,08/16/2006
60451,45,
50451,49,
40451,53,
90451,55,
00451,67,
40451,78,
40451,86,
40451,98,
40451,400,
210906,M01,10/31/2005
210906,1,10/31/2005
210906,10,10/31/2005
file contains both Null and empty so i tried with function
If IsNull(select.DATE) or select.DATE=' ' then SetNull() else StringToDate(select.DATE,'%mm/%dd/%yyyy')
still thowing the same APT_CombinedOperatorController,0: Data string ' ' does not match format '%mm/%dd/%yyyy': an integer was expected to match tag %mm. error
If IsNull(select.DATE) or select.DATE=' ' then SetNull() else StringToDate(select.DATE,'%mm/%dd/%yyyy')
still thowing the same APT_CombinedOperatorController,0: Data string ' ' does not match format '%mm/%dd/%yyyy': an integer was expected to match tag %mm. error
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
i am getting the data in the format given below
20451,1,07/28/2006
30451,1,07/28/2006
80451,10,07/28/2006
70451,15,08/16/2006
60451,45,
34567,24.09/16/2006
this is csv file so second last date field have nothing just blank....can you help how can i handle this........now i am getting same warning which i need to get rid of......
20451,1,07/28/2006
30451,1,07/28/2006
80451,10,07/28/2006
70451,15,08/16/2006
60451,45,
34567,24.09/16/2006
this is csv file so second last date field have nothing just blank....can you help how can i handle this........now i am getting same warning which i need to get rid of......
The devil is in the details, please be more precise. "Nothing just blank" means what, exactly? If the EOR is right after the comma then try checking for select.DATE='' rather than select.DATE=' ' and see if that is the magic you were missing.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Read the 3rd (date column) as Varchar(10). In transformer use the below expression. For null values we are giving a default value of '12/31/9999'; If you want null then specify SetNull();
If Trim(date_column) = '' Then StringToDate("12/31/9999",'%mm/%dd/%yyyy') Else StringToDate(date_column,'%mm/%dd/%yyyy')
If Trim(date_column) = '' Then StringToDate("12/31/9999",'%mm/%dd/%yyyy') Else StringToDate(date_column,'%mm/%dd/%yyyy')
i have tried this function also but still getting the same errorrsripathy wrote:If Trim(date_column) = '' Then StringToDate("12/31/9999",'%mm/%dd/%yyyy') Else StringToDate(date_column,'%mm/%dd/%yyyy')
APT_CombinedOperatorController,0: Data string ' ' does not match format '%mm/%dd/%yyyy': an integer was expected to match tag %mm.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 353
- Joined: Mon Jan 17, 2011 5:03 am
- Location: Mumbai, India
While reading the date value from the file, mark the column as NOT NULL and in the transformer check for empty only.
Or Use the "Null Field Value" property in the sequential file and use ''(two single quotes or Empty) as its value. Then in the transformer check for null only.
Code: Select all
If Trim(date_column) = '' Then StringToDate("12/31/9999",'%mm/%dd/%yyyy') Else StringToDate(date_column,'%mm/%dd/%yyyy')
Code: Select all
IsNull(select.DATE) then StringToDate("12/31/9999",'%mm/%dd/%yyyy') else StringToDate(select.DATE,'%mm/%dd/%yyyy')
Thanx and Regards,
ETL User
ETL User