Date conversion Error

[myukassign]

Hi,

I have a source datacolumn seldate in the form of 99991231 coming from a flatfile. Length of this field is varchar 8

I have to insert that to DB2 table using a DB2 enterprise stage. I am following the steps below.

1 In the derivation of transformer stage I am using the following function.

StringToDate(incolumn,'%yyyy%mm%dd') and My target column datatype is Date....


Everything fine...I am getting output also but the issue is I am getting a disturbing warning though I think I did all right.. Please can anyone tell me what is the reason for this warning.

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

[sohasaid]

You might miss dashes, try this:

StringToDate(incolumn,'%yyyy-%mm-%dd')

[sohasaid]

You might miss dashes, try this:

StringToDate(incolumn,'%yyyy-%mm-%dd')

[myukassign]

You might miss dashes, try this:

StringToDate(incolumn,'%yyyy-%mm-%dd')

Not actully. Since my input source don't have the format 9999-12-31, I did not use that format. Any help

[zulfi123786]

Hi,

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

The above warning comes when DataStage encounters a string that cannot be converted to date for example "99991331"---->here month is 13 which is invalid
To handle this situation do nt blindly convert dates use IsValid('Date',<column name>) function as shown below.

If IsValid('Date',<column name>) then StringToDate(incolumn,'%yyyy%mm%dd') else SetNull()

[satish.raghavan]

Check in the flat file for conversion to date. Declare the variable as DATE in the MATEDATA during extraction through Flat file and goto FORMAT-->DATE-->FORMAT STRING and give the format of incoming date there as YYYYMMDD or YYYY-MM-DD.
Note the incoming string is VARCHAR datatype but the date conversion will happen when you have mentioned the same coulmn as DATE in the Metadata and date format as specified.

This would work for a Flat file i hope.

Thanks and regards,

[myukassign]

yes its working... I put validity chk