conversion from string to timestamp

gauravrb · Post by **gauravrb** » Sun Jan 14, 2007 11:56 pm

Hi,

What might be the possible reason for the warning

"Conversion error calling conversion routine timestamp_from_string data may have been lost"

I am trying to convert the string "yyyy-mm-dd 00:00:00" in timestamp format.

what might be the possible solution for avoiding the warning?

chulett · Post by **chulett** » Mon Jan 15, 2007 12:10 am

Post your syntax.

ray.wurlod · Post by **ray.wurlod** » Mon Jan 15, 2007 12:15 am

Post your source timestamps - they may have fractional seconds.

gauravrb · Post by **gauravrb** » Mon Jan 15, 2007 12:20 am

in source there is date string "yyyy-mm-dd" to which iam concatination hard coded "00:00:00" and then converting it into timestamp.

ray.wurlod · Post by **ray.wurlod** » Mon Jan 15, 2007 12:25 am

Try appending " 00:00:00" (note the leading space character).

gauravrb · Post by **gauravrb** » Mon Jan 15, 2007 12:40 am

Tried appending " 00:00:00" with leading space also but still the same warning.

I_Server_Whale · Post by **I_Server_Whale** » Mon Jan 15, 2007 1:40 am

Do you get this warning when trying to load a database? If so, which database are you trying to load? Also, when you declared the field as timestamp, what were the length and scale defined as?

Whale.

gauravrb · Post by **gauravrb** » Mon Jan 15, 2007 1:43 am

I am trying to load the data into data set. The length of timestamp field i have kept 25 nothing else.

I_Server_Whale · Post by **I_Server_Whale** » Mon Jan 15, 2007 1:48 am

Try 19.

kumar_s · Post by **kumar_s** » Mon Jan 15, 2007 2:04 am

Hi,
What is the syntax that you have mentioned in Modify stage?

gauravrb · Post by **gauravrb** » Mon Jan 15, 2007 4:00 am

svcon=TrimF(TrimB(DecimalToString(DSLink3.MSBDAY,"suppress_zero")))

var5=If Len(svconv)=6 Then 19:svconv Else If Len(svconv)=7 Then 20:Right(svconv,6) Else "0"

If var5<>"0" Then StringToTimestamp(var5[1,4]:"-":var5[5,2]:"-":var5[7,2] : " 00:00:00","%yyyy-%mm-%dd %hh:%nn:%ss") Else "0"

chulett · Post by **chulett** » Mon Jan 15, 2007 8:09 am

I guess I should have a thicker skin than this, but how in the heck does my simple request for you to 'post your syntax' deserve a rating of '0: Off-topic / Superfluous'?

Look where we are now, you posting exactly that after someone else requested it. And it would have reveal that you were manually adding the 'time' portion to the timestamp, something you failed to mention in your original post.

gauravrb · Post by **gauravrb** » Mon Jan 15, 2007 10:14 pm

my sincere apology for that chulett, i missed your post. Yes iam appending the time part manually but do think that is the reason for which it is giving warning beacuse the same warning is given when i convert string to date.

BigFoot · Post by **BigFoot** » Wed Jan 17, 2007 5:54 am

hi gourab,

try this out...

TimestampToDateTime("your date field",StringToTime("00:00:00"))

The one which are in Bold are the datastage functions.

ray.wurlod · Post by **ray.wurlod** » Wed Jan 17, 2007 3:55 pm

Gaurav wants to use Modify stage. Those functions are Transformer stage functions.