Unable to load time value into Snowflake table

[JSD03]

Hello,

I'm unable to load a time value into a Snowflake table (field defined wth the type "time"). Im' using Talend ESB 8.0.1.

Is there a way to achieve that?

I've tried many things:

• java Date type with Date schema (and HH:mm:ss pattern)
• Java Time type with Object schema
• java String type with String schema (after having transformed the value with TalendDate.formatDate function)

But I always get the same error message : SEVERE: State: COPY_INTO_TABLE, COPY INTO "MYTABLE_20220128_113324_058_2" FROM '@~/MY_TABLE/INSERT/20220128_113324_058_BCUCOG_2' on_error='ABORT_STATEMENT' file_format=( field_optionally_enclosed_by='"' empty_field_as_null=true), Time '1970-01-01 15:45:00.000+01:00' is not recognized

It seems that Talend send anyway a Date.

Thanks for your help

[Anonymous]

Can you show how your job is configured regarding this section? It would be useful to see the output schema (from the previous component) and the expected schema (from the Snowflake db component). I have just tried this out and it worked without issue. FYI the Date object in Java will always hold a complete date+time. It is essentially an object that holds a number.

[JSD03]

Hello,

See attached screenshots :

Snowflake schemaJob design :

Schema (input/output of the tMap) :

 

[Anonymous]

I cannot reproduce this at all I am afraid. I will send you a private a message. If you can send me your Snowflake table schema DDL to me via email (so that you are not sharing it here and ONLY if you are happy doing it), I can recreate your table and try to emulate what you are doing as closely as possible. Just so that you are aware, I have tried various things to try and cause this to fail, but it works every time.

[JSD03]

Okay... I have sent all the material I have by email. Hop it will help you to reproduce.

[Anonymous]

Hi @Jean-Sébastien DARGES​,

I've not received anything as yet. It's not stuck in your outbox is it?

Regards

Richard

[JSD03]

Strange: the email has been sent. Maybe the content type of the attachments has been blocked by your email server. Anyway I have sent to you a new email 😉

[Anonymous]

I still have no issue loading data. Can you show me the complete date format you have in your OUT__client.DATE_VENTE column date format?

[JSD03]

The exact same that I have sent to you in the tMap :

• input "yyyy-MM-dd'T'HH:mm:ss" (same issue if I use "HH:mm:ss")
• output "HH:mm:ss"

Do you test that with the exact same version of Talend I'm using? (TOS ESB 8.0.1)

Can we schedule a web call in order to show you?

[Anonymous]

I am using the exact same version (ESB is my favourite of the TOS platforms). I've also tried it on the Enterprise Edition. I am using a Mac, but that shouldn't make any difference regarding this.

I have tried out the exact same date formats and it just won't fail for me.

What version of Java are you using?

Oh, another thought. Where is your date coming from? Is it from the source table (at the beginning of the job) or is it the first lookup table? Wherever it is coming from, can you show me an example of the dates that are returned and the format they are returned in?