String to Timestamp Conversion

[sankawa]

Hi,

I am using CSV file as Source and Oracle as Target.Now i want to convert string to timestamp.The source data format is dd/mm/yyyy and the required format is yyyy-mm-dd hh:nn:ss.when i tried it showing the following Message:
Conversion error calling conversion routine timestamp_from_string data may have been lost

[gateleys]

Hi,

I am using CSV file as Source and Oracle as Target.Now i want to convert string to timestamp.The source data format is dd/mm/yyyy and the required format is yyyy-mm-dd hh:nn:ss.when i tried it showing the following Message:
Conversion error calling conversion routine timestamp_from_string data may have been lost

Code: Select all

Oconv(Iconv(InputDate, "D/DMY[2,2,4]"), "D-YMD[4,2,2]")

During the write step, the time portion will be taken care of.

[sankawa]

Hi
Thks for replying.but i am using parallel job Transformation and Unix operating system.Plz solve my Query.

[chulett]

Plz post your complete Derivation.

[sankawa]

Thanks for your speedy reply .
Actually My input is a CSV file am reading in Sequential stage ... and Transition_dt is one of the input column in dd/mm/yyyy and Iam passign this value through Transformer and my Traget is Oracle where datatype of same field is Timestamp ....
So i need to convert my input String into time stamp ..
I tried Stringtotimetamp convertion available in PX transformer but Am getting error like lost Data


Thanks
Sandeep

[chulett]

Plz post your complete Derivation.

Since you didn't do that, I'll ask again. And now that other details are leaking out, clarify something. Your Oracle target field is a Timestamp in the job but what is it in the target table - DATE? TIMESTAMP? Plus - what stage are you using for the Oracle piece of this?

[Mhasan]

Chulett ,
I reply from the behave of sandeep .. as we both had same issue ....
what is it in the target table - DATE? TIMESTAMP
Answer :-- Timestamp
what stage are you using for the Oracle piece of this?
Answer :-- Oracle stage in PX

[sankawa]

hulett,
This is the information required by you...
what is it in the target table - DATE? TIMESTAMP
Answer :-- Timestamp
what stage are you using for the Oracle piece of this?
Answer :-- Oracle stage in PX

and the other thing the job is successfully run with some warning. but it doesn't updating the target table and show the warning-- conversion may cause Data Lost...

Regards,
Sandeep

[chulett]

Not quite there.

what is it in the target table - DATE? TIMESTAMP?
Answer :-- TIMESTAMP.
Question :-- to what level of precision?

what stage are you using for the Oracle piece of this?
Answer :-- Oracle stage.
Question :-- which Oracle stage?

Last but not least, perhaps third time will be the charm: Please post your complete derivation as a starting point.

[sankawa]

hi

Question:-- to what level of precision?
Ans:--Without Precision
Source format is dd/mm/yyyy(String) and target format is Timestamp it doesn't matter the format in target but it should be Timestamp value...
Question :-- which Oracle stage?
Ans:--Oracle Enterprice

This is the complete vale i specified in the transformation.
Column Name SQL-Type Length Scale Nullable Description
VALUDATION_DT Timestamp - - Yes Nullable TimeStamp

I tried in two way:---
First way is-------
I create one staging variable mydate(String) and use the rite the Query:--
SrcCorpriceSf.VALUATION_DT[7,4] : "-" : SrcCorpriceSf.VALUATION_DT[4,2] : "-" : SrcCorpriceSf.VALUATION_DT[1,2] : " 00:00:00"
and after that in VALUDATION_DT feild i wrote the following Querry:--
StringToTimestamp(mydate,"%yyyy-%mm-%dd %hh:%nn:%ss")

Second way is-----
I wrote the querry directly to VALUDATION_DT feild:--
StringToTimestamp(SrcCorpriceSf.VALUATION_DT[7,4] : "-" : SrcCorpriceSf.VALUATION_DT[4,2] : "-" : SrcCorpriceSf.VALUATION_DT[1,2],"%yyyy-%mm-%dd %hh:%nn:%ss")
But data is not loadinfg to thr target Table...

Sorry i can't give you more information abt this issue,actually i am new to Data Stage,plz try to understand my problem and solve it.