DSXchange

Hi all.

I am loading a DB2 table via the ODBC stage
The source file is ascii text and the source field is varchar 255
The dates in the field are in yyyy-mm-dd format

In the transformer stage I am using the following code:
StringToDate(Trim(lk_SourceFile.DateFld),'%yyyy-%mm-%dd')

The job fails with the following message:
APT_CombinedOperatorController,7: Data string '' does not match format '%yyyy-%mm-%dd %hh:%nn:%ss': an integer was expected to match tag %yyyy.

Thanks in advance for your help.

Somewhere in your file you've got at least one value that doesn't match that format. It's also a little confusing that a time portion is mentioned in the error when you are trying to convert to a date.

Still it doesn't seem to like a year that it found.

Thanks for replying craig. I had the same concerns you mentioned in your reply so I delete all but one record from the file and have confirmed the data. Still receiving the message.

Any suggestions from anyone would be greatly appreciated.

Post some examples of your actual data. What is the datatype of the target for the StringToDate function? Does it help if you append a zero time to the DateFld before you convert it?

data example:
|xxx|xxx|2009-12-15|xxx|xxx|

target data type:
Date length 10

append a zero time to cause an error as well.

to validate the date I removed the ODBC stage add a Seq Stage and wrote all records to a file that passed the test below. All records passed.

IsValid('Date', lk_SourceFile.DateFld) = 1

Interesting. It all looks good so far... what is the datatype of the target field in DB2? Unfortunately, I have no DB2 here to validate anything against so if this is specific to DB2 I'm not going to be much help here.

Date length 4. Thanks.

I'm not sure about the ODBC stage but setting the data type to varchar and the length to 10 resolved this sort of error when using date and timestamp fields with DB2 API stage. It was a long while ago and version 7x. Hope that helps.