Type Conversion Error ..String to Date Function

[Developer9]

Hi ,

I am reading the date field as Varchar using Text file .
Input format: mm/dd/yyyy

Target :DB2 database -DATE format


Trasformer logic :

Code: Select all

StringToDate(Input.DT,"%mm/%dd/%yyyy")

Error message :

Code: Select all

APT_CombinedOperatorController,0: Data string '11/3/2012' does not match format '%mm/%dd/%yyyy': the value for tag %dd has fewer characters than expected.
Conversion error calling conversion routine date_from_string data may have been lost

Please let me know anything wrong this logic

Thanks

[chulett]

That mask of %dd says that there will always be two characters in each day number. You need to let it know there could be one or two characters. How to do that is all spelled out in the Parallel Job Developer's Guide where it lists the Date and Time format strings.

[Developer9]

@chulett

I changed my input format to 11/03/2012 (mm/dd/yyyy) and re-ran my job ..now I don't see any issues.

I will do more work-around on these dates and keep posted

Thanks

[chulett]

That's not a "work around", that's one solution for this issue. I've already mentioned the other.

[Developer9]

Thanks chulett

Work around ..I meant here is to do with different format for date.

Now I have different format of string as

INPUT (Sequential file ) :YYYYMMDD VARCHAR 8
TARGET DB2:DATE (4)

Transformer function used :

Code: Select all

StringToDate(Input.column,"%yyyy%mm%dd") 

Also I tried using Substring

Code: Select all

Inputlink.colname[1,4]:'-':Inputlink.colname[5,2]:'-':Inputlink.colname[7,2]

Code: Select all

Error :

APT_CombinedOperatorController,0: Conversion error calling conversion routine date_from_string data may have been lost

SQLExecute reported: SQLSTATE = 22007: Native Error Code = -180: Msg = [IBM][CLI Driver][DB2/AIX64] SQL0180N  The syntax of the string representation of a datetime value is incorrect.  SQLSTATE=22007

I am able to insert records for date fields manually in DB2 control in the format yyyy-mm-dd

I search this topic in the forum ..I was not able to fix this issue
.. :D

Thanks for the input

[ssnegi]

StringToDate(Inputlink.colname[1,4]:'-':Inputlink.colname[5,2]:'-':Inputlink.colname[7,2],"%yyyy-%mm-%dd")

This is working correctly for me. Check the data to see if there are any errors like nulls, wrong format etc

[ray.wurlod]

StringToDate(Inputlink.colname[1,4]:'-':Inputlink.colname[5,2]:'-':Inputlink.colname[7,2],"%yyyy-%mm-%dd")

This is working correctly for me. Check the data to see if there are any errors like nulls, wrong format etc

That won't work for "2012/3/11".