Oracle datetime output in wrong format

[Anonymous]

Hi Talend Community,

I queried a oracle DB, using the following query

.o_query(above is the schema i defined for the oracledb)

Where i get the 2 values and convert the table times to utc.

Underneath you see the output, for the query.

o_output

I would like to send this to a sql db. i used the components toracleinput -->tmap-->tmssqloutput

Underneath you see the tmap i configured.

tmap

This is the mssql db schema i configured

sql_typeou

output

The question i have is the following:

i wrote to a csv file first to check the values that were going to be send to the msql db.

As you can see this doesnt reflect the 2nd picture(query output).

I think the problem is in the dates that come from the oracleinput, but i cannot figure out where this could come from and how to resolve this.

Is there something that i didnt configure right, or something that i oversee.

[Anonymous]

Hi,

    Since the data from input area is already in date format, I don't think you have to parse again. The data will automatically reallign to new format since its in date data type. Please also note that you are parsing the date at output area in one format but underlying schema is in different format.

I would recommend you to use tLogrow to test the output after each component to make sure that you are getting right output. Once you are sure that data is correct, you can remove them.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved

[Anonymous]

Hi Thanks for the reply.

With the tlogrow i get the following output.

 

as you can see this does not match the toracleinput query result. I am missing the sop_start_utc time and the stop_end_utc time is in a weird format.

How can i solve this

[Anonymous]

This is the output i get when i connect the tlogrow to the toracleinput

[Anonymous]

Hi,

    In the Oracle query, you are using to_char function to convert the entrire data to String. Could you please share the schema of the tOracleInput? I would like to double check whether its in same format. When I saw the schema for tMap, it was showing as date function.

     In short, the first step will be to make sure that the data types are correct for the input component and see whether its printing correctly from the input component. There is nothing wrong in taking the date value as string from DB. You can convert the column to date data type in later stage using either tConvertType component or using Talend parse date functions. 

    I think once you resolve this issue, you should be able to resolve the underlying problem. 

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved