DSXchange

Posted: **Tue Jan 21, 2014 4:12 pm**

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

Posted: **Tue Jan 21, 2014 4:19 pm**

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.

Posted: **Wed Jan 22, 2014 7:56 am**

@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

Posted: **Wed Jan 22, 2014 8:22 am**

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

Posted: **Thu Feb 13, 2014 4:05 pm**

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

Posted: **Thu Feb 13, 2014 6:52 pm**

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

Posted: **Thu Feb 13, 2014 7:30 pm**

ssnegi wrote: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".

DSXchange

Type Conversion Error ..String to Date Function

Type Conversion Error ..String to Date Function

Reply

Re: Reply