Update not updating rows based on datetime comparison?

rp2018 — Mon, 27 Aug 2018 16:30:48 GMT

My source table and destination table is same.

DB is Azure Sql server.

Job layout: source--->tmap--->tmssqlouput--->tmssqlcommit

Column used to compare is LDTS which is datetime coming from Ms sqlserver

In tmap, I've defined LDTS as follow, but none of them are working.

"yyyy-MM-dd HH:mm:ss.SS"

"yyyy-MM-dd HH:mm:ss.SSS"

"yyyy-MM-dd HH:mm:ss.aaa"

There is a data truncation happening with the update process, if the date

is 2018-08-27 09:49:49.620 then the comparison works, but if the date is

2018-08-27 09:47:03.353 comparison won't work. How can we fix this?

NOTE: tlogrow is showing correct output results from tmap, but tmssqloutput is not updating the rows.

| tLogRow_1 |
|=----------------------+-----------------------+------=|
|LDTS |LEDTS |LEDTS_1|
|=----------------------+-----------------------+------=|
|2018-08-27 10:09:25.347|2018-08-27 10:25:17.715|null |
|2018-08-27 10:09:25.347|2018-08-27 10:25:17.731|null |
|2018-08-27 10:09:25.347|2018-08-27 10:25:17.731|null |
|2018-08-27 10:09:25.347|2018-08-27 10:25:17.731|null |

Re: Update not updating rows based on datetime comparison?

Jesperrekuh — Mon, 27 Aug 2018 23:58:44 GMT

this format is the correct (Java) one: "yyyy-MM-dd HH:mm:ss.SSS"
I dont fully understand the update process... from your writing I suggest take a look at the 3 date types in MSSQL and the one your output table is using/created... DATE, DATETIME, DATETIME2

For what I can recall DATETIME2 is able to store more seconds precision and a wider date range.
Talend uses Datetime2 (Im not sure)

topic Re: Update not updating rows based on datetime comparison? in Talend Studio

Update not updating rows based on datetime comparison?

Re: Update not updating rows based on datetime comparison?