DSXchange

Hi,

I have 'X' field in my job it is coming in as a varchar(16) we are converting it as
timestamp in the transformer .We don't have any conversion problem.
some reason we couldn't load the data into the main table.I Know the problem is with this field only. When I try to load with the current timestamp for this 'X' field it's loading.
I found that there is somedata in this field of Hours portion has >23 value and in the Seconds >60 so I tried to run the job to handle this with the fallowing function

If X [9,2]>24 or X[11,2]>60 Then '00' Else Trim(StringToDate(X[1,8],"%yyyy%mm%dd") : ' ' :
StringToTime(X[9,4] : "00" ,"%hh%nn%ss"))

But this is not working too.
Do you have any other thaughts.

Thanks.

Take a minute and review your if then else statement. If the hour and seconds are greater than 23 and 59 respectively, then 00.

What does the source "timestamp" look like? VarChar(16) is quite an unusual data type for a timestamp.

Verify that you're getting the correct source data and results by creating a small test job that sends its output to a text file, and investigate that file.

Check your If then Else statement, you are assigning '00' to the timestamp field itself. Use stage variable to adjust that before doing the stringto timestamp conversion.