StringToDate does not match format '%yyyy-%mm-%dd %hh:%nn:%s

tbtcust · Post by **tbtcust** » Tue Dec 08, 2009 12:57 pm

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.

chulett · Post by **chulett** » Tue Dec 08, 2009 1:12 pm

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.

tbtcust · Post by **tbtcust** » Tue Dec 08, 2009 1:21 pm

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.

chulett · Post by **chulett** » Tue Dec 08, 2009 1:27 pm

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?

tbtcust · Post by **tbtcust** » Tue Dec 08, 2009 2:39 pm

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

chulett · Post by **chulett** » Tue Dec 08, 2009 2:54 pm

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.

tbtcust · Post by **tbtcust** » Tue Dec 08, 2009 3:26 pm

Date length 4. Thanks.

vinnz · Post by **vinnz** » Tue Dec 08, 2009 5:07 pm

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.