topic Talend DATETIME2 auto rounding a value in Talend Studio

Talend DATETIME2 auto rounding a value

rdentsu — Fri, 15 Nov 2024 22:32:34 GMT

Hi, all. I have I have SQL Server input and output components with some DATETIME2 column, however the column with DATETIME2 data type in the resulting destination table is different from source. Example if the source like this "2022-04-21 23:48:23.1680000", but the result on destination table like this "2022-04-21 23:48:23.1666667".

Can you guys explain and help me to fix this issue? if I can't get 7 digit in the last DATETIME2, at least the result same with source. like this "

2022-04-21 23:48:23.168" is not problem for me, but with data type is still DATETIME2. And Date Pattern schema I specified the format like

"yyyy-MM-dd HH:mm:ss.SSSSSSS" and I give the precision "7".

Thank you.

Re: Talend DATETIME2 auto rounding a value

Anonymous — Wed, 21 Sep 2022 02:29:40 GMT

Hello,

Is the data in the resulting table in DATETIME format rather than DATETIME2?

As far as we known, Java don't support for six but only for the millisecond sss.

If you want to show six, write .SSSSSS and the other part will be Auto completed with "000".

Please take a look at this article: https://docs.oracle.com/javase/tutorial/i18n/format/simpleDateFormat.html

Best regards

Sabrina

Re: Talend DATETIME2 auto rounding a value

rdentsu — Wed, 21 Sep 2022 06:17:28 GMT

I have do that, on the metadata schema I already use this date pattern "yyyy-MM-dd HH:mm:ss.SSSSSSS" with precission is 7 (you can look the image below), but the output like rounding the value rather than the original value.

from this : "2022-04-21 23:48:23.1680000"

to this : "2022-04-21 23:48:23.1666667"

Re: Talend DATETIME2 auto rounding a value

Anonymous — Wed, 21 Sep 2022 07:15:00 GMT

I have made some testing with studio 8.0.1 and confirm that this issue, a solution to fix this issue as below:

1- Read the data with string type.

2- Convert the string data to a java.sql.Timestamp object which supports nanosecond on a tJavaFlex.

Please try and let me know if it works.

Regards

Shong

Re: Talend DATETIME2 auto rounding a value

rdentsu — Wed, 21 Sep 2022 09:30:54 GMT

Hi Shong, thank you for the suggest, here I found after try your suggest.

I already use open source JTDS, and I use Talend v7.2.1
I tried to your suggest number 2, but when I ran the job, the error message said "cannot convert from Object to Date". Am I missing something? because I want to column still on datetime2 in SQL Server. Is it needed to convert Object to Date before the output component?

Thankyou

Re: Talend DATETIME2 auto rounding a value

Anonymous — Wed, 21 Sep 2022 11:15:16 GMT

It is a compilation error, have you wrote the java code on tJavaRow?

java.sql.Timestamp ts=java.sql.Timestamp.valueOf(input_row.startDate);

output_row.startDate=ts;

//startDate is column name, change it to expired_payment in your case.

Re: Talend DATETIME2 auto rounding a value

Anonymous — Wed, 21 Sep 2022 11:16:25 GMT

Can you also try with version 8.0.1?

Re: Talend DATETIME2 auto rounding a value

rdentsu — Wed, 21 Sep 2022 11:31:24 GMT

I already write the code like your suggest, but the error still the same.

for trying with version 8.0.1 is almost difficult because on our production still use 7.2.1, and if want to upgrade to 8.0.1 maybe to consuming time and many decision to make it. so, I try to find solution on Talend 7.2.1 if capable.

Re: Talend DATETIME2 auto rounding a value

Anonymous — Wed, 21 Sep 2022 14:04:57 GMT

I have checked with open studio 7.2.1 again and I don't have the compilation error, I am using Java 11. Below is my testing results:

JTDS driver (KO)

source data: 2022-04-21 23:48:23.1680000

target data: 2022-04-21 23:48:23.1666667

Microsoft driver (OK)

source data: 2022-04-21 23:48:23.1680000

target data: 2022-04-21 23:48:23.1680000

So, you need to use Microsoft driver.

If still have the compilation error, can you export the job and send it to me via email shong@talend.com? I will look into it.

Regards

Shong

Re: Talend DATETIME2 auto rounding a value

rdentsu — Thu, 22 Sep 2022 03:24:09 GMT

Thankyou for your suggestion, I tried to use Microsoft Driver and the output data was the same with input data.

source data: 2022-04-21 23:48:23.1680000

target data: 2022-04-21 23:48:23.1680000

Because new on Talend and the existing project from my colleague only use JTDS Driver, so I new thought only change the connection driver will be usefull.

And can I know which version JTDS Driver and Microsoft Driver use in Talend 7.2.1 or how I check it? because after I try to install new Talend 7.2.1 on another device, the driver is not installed and i can't download or install directly from Talend.

thankyou for your answer, it help me a lot 🙂

Re: Talend DATETIME2 auto rounding a value

Anonymous — Thu, 22 Sep 2022 05:00:26 GMT

below are the jar location folders where you can check the driver versions which are used by studio.

JTDS:

<studio installation directory>\configuration\.m2\repository\net\sourceforge\jtds\jtds\1.3.1-patch-20190523

Microsoft driver:

<studio installation directory>\configuration\.m2\repository\org\talend\libraries\mssql-jdbc

Re: Talend DATETIME2 auto rounding a value

rdentsu — Thu, 22 Sep 2022 07:40:52 GMT

I see, thankyou very much Mr. Shong

Re: Talend DATETIME2 auto rounding a value

rdentsu — Fri, 23 Sep 2022 03:10:41 GMT

Hi, Shong. I have another question about datetime2 using Microsoft Driver. when the input like this:

Source data : 2021-03-18 13:46:43.5363840

target data : 2021-03-18 13:46:43.5360000

data from .536 was converted to 0000, are you have any suggestion to make the data still like Source (.5363840)?