One Star

Unable to retrieve Timestamp(6) formatted datetime from Teradata table

I am new to Talend.
I tried connecting to Teradata. I have my datetime columns defined as Timestamp(6), like "2013-03-11 16:02:24.997543". But I am unable to read entire nano seconds part. I am able to give "yyyy-MM-dd HH:mm:ss.SSS" and can get first 3 digits in my nano seconds. But if I tried giving "yyyy-MM-dd HH:mm:ss.SSSSSS", I am getting values like this - "2013-03-11 16:02:24.000543" (first three digits being replaced with 0s). Is there any other datetype string (other than SSS) to represent nano seconds?
Thanks
Nishanth
8 REPLIES
Moderator

Re: Unable to retrieve Timestamp(6) formatted datetime from Teradata table

Hi,
Which talend version do you used? I think this related forum could be helpful for you:Forum 28436.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Unable to retrieve Timestamp(6) formatted datetime from Teradata table

Hi Sabrina,
I am using TOS Big Data 5.2.2
I have seen that post and I guess it is about .SSS (3 milli seconds) which I was already able to get. But our Teradata tables are defined with Timestamp(6) which uses 6 digits in milli seconds place. Is there a possible way to get this in v5.2.2? Please help me.
Thanks
Nishanth
Moderator

Re: Unable to retrieve Timestamp(6) formatted datetime from Teradata table

Hi,
Unfortunately, java does not support for six and only support for the millisecond sss.
If you want to show six, write .SSSSSS and the other part will be Auto completed with "000".
For example, the input is:
17-APR-12 07.42.57.811804 AM
output:
17-APR-12 07.42.57.811000 AM
Please see the related reference http://docs.oracle.com/javase/tutorial/i18n/format/simpleDateFormat.html
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Unable to retrieve Timestamp(6) formatted datetime from Teradata table

Thanks Sabrina,
I think I need to start using the appended 000's to support my Data Base.
Thanks
Nishanth
Moderator

Re: Unable to retrieve Timestamp(6) formatted datetime from Teradata table

Hi,
It is a solution, but not sure you can get the exactly value ""2013-03-11 16:02:24.997543"". Perhaps the result seems ""2013-03-11 16:02:24.997000"".
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Unable to retrieve Timestamp(6) formatted datetime from Teradata table

Hi,
I am new to the talend and i am getting the below error
Exception in component tSalesforceOutput_1
java.lang.Exception: org.apache.axis2.AxisFault: '14-05-2013' is not a valid value for the type xsd:dateTime
Thanks,
Satish Kumar
Moderator

Re: Unable to retrieve Timestamp(6) formatted datetime from Teradata table

Hi satishkumar,
From your error info, it seems that something wrong with your date type
Could you give us your work flow and the component setting screenshots? What is your input and output value?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Unable to retrieve Timestamp(6) formatted datetime from Teradata table

Hi Sabrina,
we are getting the below error while importing data from Oracle in to teradata input. Can you please help us on this.
Exception in component tTeradataOutput_1
java.lang.NullPointerException
at new2.orcaletotd_0_1.orcaletotd.tOracleInput_2Process(orcaletotd.java:706)
at new2.orcaletotd_0_1.orcaletotd.tOracleConnection_1Process(orcaletotd.java:408)
at new2.orcaletotd_0_1.orcaletotd.runJobInTOS(orcaletotd.java:1486)
at new2.orcaletotd_0_1.orcaletotd.main(orcaletotd.java:1351)
Thanks,
kaarthik