topic Re: Talend JTDS Driver cannot insert datetime2 in Talend Studio

Talend JTDS Driver cannot insert datetime2

rdentsu — Fri, 15 Nov 2024 22:30:47 GMT

Hi all,

I have SQL Server input and output components with some DATETIME2 column

table, there is data like `0077-07-07 00:00:00`

maybe because previous worker didn't validate the datetime so the data turned out like that

And then I tried to send the data to destination using JTDS driver, when I ran the Talend Job, error message came with text '

Conversion failed when converting date and/or time from character string.'. On advance setting in output I check the option 'Ignore date out of range'.

I wonder, how to pass that data using JTDS Driver? because I want to use JTDS Driver if possible.

Re: Talend JTDS Driver cannot insert datetime2

Anonymous — Mon, 03 Oct 2022 09:20:26 GMT

Can you also share what is the columns schema type in Talend, is it String or Date?

Re: Talend JTDS Driver cannot insert datetime2

rdentsu — Mon, 03 Oct 2022 10:01:13 GMT

its Date type with DATETIME2 for db type. you can see image below

Re: Talend JTDS Driver cannot insert datetime2

NCrawford1658815487 — Mon, 03 Oct 2022 11:15:55 GMT

Your content helped me a lot to take my doubts, thank you very much.

Re: Talend JTDS Driver cannot insert datetime2

Anonymous — Mon, 03 Oct 2022 11:33:24 GMT

I'd give a try and set it up as String and convert the Date to String via yyyy-MM-dd pattern then hopefully this will reach the database as a String and the database auto-convert it via an ISO pattern.

From the error message I'd also double check if this happens in the input or the output component.

Theoretically the Date type should work fine.

Re: Talend JTDS Driver cannot insert datetime2

rdentsu — Tue, 04 Oct 2022 02:12:58 GMT

so the output schema is String? or still datetime2? because I want the scheme to be as datetime2 as possible.