Talend DATETIME2 auto rounding a value

[rdentsu]

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.

[Anonymous]

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

[rdentsu]

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"

[Anonymous]

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

[rdentsu]

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

1. I already use open source JTDS, and I use Talend v7.2.1
2. 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

[Anonymous]

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.

[Anonymous]

Can you also try with version 8.0.1?

[rdentsu]

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.

[Anonymous · Accepted solution]

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

[rdentsu]

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 🙂