StringToTimestamp conversion in Transformer

[kogads]

Hey,

I am trying to convert a varchar(8) field to Timestamp in Transformer
Input Data for that field:20111113(yyyymmdd)
So, i had StringToTimestamp(<Field-name>,"%yyyy%mm%dd")

During Runtime, I have the following Fatal error::::

Caught exception from runLocally(): APT_ParseError: Parsing parameters "%yyyy%mm%dd" for conversion "timestamp=timestamp_from_string[%yyyy-%mm-%dd %hh:%nn:%ss](string)": APT_Conversion_String_TimeStamp: Invalid Format [%yyyy%mm%dd] used for string_from_time type conversion.

Any inputs please.......

Thanks.

[pandeesh]

I think youre source string contains any values for %hh:%nn:%ss part.
SO you are getting that error.
If you use, StringtoDate() for your data, it will work.Or else use like:

Code: Select all

StringToTimestamp(inputlink:"000000","%yyyy%mm%dd%hh%nn%ss")

I hope the above will work.

Thanks

[kogads]

I have put a peek stage to verify if its a data issue..But the field has value as YYYYMMDD.
FYI:After the conversion from StringtoTimestamp is done in transformer.The value should be inserted into Oracle table

Also, I have tried the code you have suggested.
Now, i have some warnings saying::
Transformer:Conversion error calling conversion routine timestamp_from_string data may have been lost
And then a Fatal Error saying ::
Oracle_Enterprise_Stage:FatalError:Invalid Time

But thanks for the suggestion.Logically your code should work

[pandeesh]

can you please try like this?

use the below in transformer:

Code: Select all

StringtoDate(input,"%yyyy%mm%dd")

in your oracle target stage, specify the column as Timestamp.

[kogads]

Whenever you change the Datatype in target oracle stage it automatically reflects back in Transformer stage.
FYI:Timestamp in oracle table is as follows
03-NOV-11 12.00.00.000000000 AM

Is this something i need to concern about

[pandeesh]

Have you tried with StringToDate as i mentioned?

Actually your source have only %yyyy%mm%dd right?
So, you don't have any timestamp part in your data?
So, what you are expecting for timestamp ?
Is this 00:00:00?

[pandeesh]

I believe the below should work:

Code: Select all

StringToTimestamp(inputlink:'00:00:00','%yyyy-%mm-%dd %hh:%nn:%ss') 

[kogads]

Yes, i have tried that way..But as i mentioned whenever you change a target datatype it in turn changes the preceeding stage datatype in our scenario.
Also, for the code you mentioned
StringToTimestamp(inputlink:'00:00:00','%yyyy-%mm-%dd %hh:%nn:%ss')
It won't work as the in this function, we are mentioning the input format as second argument and not the output format that we would like it to appear

[kogads]

Yes i am expecting it to be 00:00:00
So, the code below should work
StringToTimestamp(inputlink:'00:00:00','%yyyy%mm%dd %hh:%nn:%ss')

But, i am getting the same error
Transformer:Conversion error calling conversion routine timestamp_from_string data may have been lost
And then a Fatal Error saying ::
Oracle_Enterprise_Stage:FatalError:Invalid Time

[kogads]

Hey Successful....Thank you so much for the help.The code
StringToTimestamp(inputlink:'00:00:00','%yyyy%mm%dd %hh:%nn:%ss')
has worked

[pandeesh]

What did you make wrong in the previous attempt?
What about StringToDate?Have you tried like that?

[kogads]

StringtoDate would not work as that field is defined as Timestamp in the metadata for Oracle table. If you put StringtoDate and define the field as Timestamp it would not take it.And, if you define the datatype as Date...down the line again you need to convert the Date format to Timestamp format. I have tried it. But was unsuccessful as i explained.