topic Re: datetime to timestamp conversion in Talend Studio

datetime to timestamp conversion

Anonymous — Wed, 31 Oct 2018 09:24:43 GMT

Hi, I'm new to Talend I have no idea about datetime to timestamp conversion I tried changing date pattern in expression column for the target table in tmap, no error and migration worked fine but then no date value stored in the new column for date (because it's a timestamp db type) it's all null, is there any way I can do to solve this?

Thanks!

Re: datetime to timestamp conversion

TRF — Wed, 31 Oct 2018 13:05:32 GMT

What's the DB engine?

Can you share the expression you're using with a sample of input data?

Re: datetime to timestamp conversion

Anonymous — Wed, 31 Oct 2018 15:28:59 GMT

Oracle as target and Informix as input.

dtcreatedate (datetime) -> i'm just using row1.dtcreatedate in the expression column for createdate (timestamp). I tried turning the datetime values into string and parse it into date format again but nothing works and createdate (timestamp) in oracle db is still null.

Re: datetime to timestamp conversion

Anonymous — Wed, 31 Oct 2018 16:15:28 GMT

I think you need to show us screenshots of your job and your component configurations for the components which are using/manipulating this date.

Re: datetime to timestamp conversion

Anonymous — Wed, 31 Oct 2018 16:30:27 GMT

input schema:

output schema:

im also not really good with Java so i cant write the exact expression for timestamp conversion, so I just simply map it like this for the date column and it didnt store anything.

ETA: for example data from input column is 2018-07-10 16:46:33 and it wasnt inserted into target column, just null value.

Re: datetime to timestamp conversion

Anonymous — Wed, 31 Oct 2018 16:37:08 GMT

Can you remove your target DB component and replace it with a tLogRow. Then run the job and let us know what you get?

Re: datetime to timestamp conversion

Anonymous — Wed, 31 Oct 2018 16:42:38 GMT

i've tried that before, on tLogRow i could see the date values but it didnt work when i connected tmap to the target db i think it's because oracle db has timestamp and it rejects datetime value?

Re: datetime to timestamp conversion

Anonymous — Wed, 31 Oct 2018 16:51:46 GMT

You are actually converting from a Java Date object to an Oracle Timestamp, so it is not to do with Datetime to Timestamp. If you are seeing dates output by the tLogRow, it means the Date is available in the job. Are you getting any errors in the job and did you build it or inherit it from someone else? The reason i ask is that you can configure db components to omit to insert values. If you have no error and you didn't create this job, I suspect the original developer has chosen not to insert the date values. Can you post a screenshot of the job as well?

Re: datetime to timestamp conversion

Anonymous — Wed, 31 Oct 2018 17:28:06 GMT

this is the job:

I'm just working on the data migration I created the db but I wasn't the one who designed it, that also means I can't change db components to have all the values stored without informing the original developer. And i didn't get any error after running a job it's just that the date value can't be stored to the target db. Is there any way to convert java date object to oracle timestamp?

Re: datetime to timestamp conversion

Anonymous — Thu, 01 Nov 2018 16:06:13 GMT

Can you go to the advanced settings of your DB component (output) and show us what you have there. My suspicion is that your problem may be caused by the field options section

Re: datetime to timestamp conversion

Anonymous — Fri, 02 Nov 2018 03:51:41 GMT

Here's what I have:

Is the 'Use Timestamp for Data Type' causing it to fail entering the output db?

Re: datetime to timestamp conversion

Anonymous — Fri, 02 Nov 2018 12:12:17 GMT

You could try unticking that.

How do you know that the data is not getting to the target database? Are you able to query the target database? The problem I have with what you are telling me is that you are saying that data is being sent, but the date is not arriving, yet there are no errors at all. This suggests that either the column is being supplied as null or that maybe the date column you are checking is not the correct one.

When you run the job, can you put a tlogrow between the tMap and the Oracle component and ensure the tLogRow is configured to Mode=Vertical. This will tell you the name of each column and the value each holds. Then check your database, find the row and check every column in the row.