String to TimeStamp Conversion

[nima511]

I have an input field as below
date_opened
2010-01-05 13:14:16.130

I am reading it as a Varchar and I have also tried Varchar(26).
In my transformer I use :
StringToTimestamp(DSLink35.date_opened,"%yyyy-%mm-%dd %hh:%nn:%ss")

My output is a sequential file and the column is defined as a timestamp.
My problem is :
1) If the input has a value it seems fine but when there is no data on the input my output looks like "*******************". How can I see just a blank instead of the *'s.
2) And eventually I would want to load this into a SQL Server and I am getting an error with the str to ts conversion.

[anbu]

Code: Select all

If Trim(DSLink35.date_opened) = '' Then '' Else StringToTimestamp(DSLink35.date_opened,"%yyyy-%mm-%dd %hh:%nn:%ss") 

[nima511]

Anbu, Do you mean
If Len(Trim(DSLink35.date_opened) = 0) Then ''" Else StringToTimestamp(DSLink35.DT_OPENED,"%yyyy-%mm-%dd %hh:%nn:%ss")

[anbu]

Yes

[nima511]

I am still getting the same output.

[anbu]

If you are writing to a file, then you dont need to use the function StringToTimestamp. Timestamp is considered as string in text file

Try removing the function and run the job

[vinothkumar]

Please post the warning message that you are getting.

[nima511]

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

[ray.wurlod]

Have you got a StringToDate() function in a different derivation?

The error message talks about date_from_string, not timestamp_from_string.

Also, you need a timestamp format string that allows for fractional seconds if you wish to keep these.