Data Structure for Time&Date to a Database

KNIME Analytics Platform
1

I'm writing a table to one of our databases and having an issue matching the data structure for date.

The error I'm receiving is "ERROR Database Writer      0:237      Execute failed: Column "report_date" of type "Local Date" from input does not match type "date" in database at position 2"

Is there a way to convert Local Date to date?

Thank you

2

Hi,

Which version of KNIME are you using, and what is the database you are writing to? Can you try to manually set the type in the "SQL Types" tab of the Database Writer?

Cheers,

Roland

3

Hi Roland,

The version of KNIME I'm using is 3.4.2.

The database I'm writing to is Postgres.

I've tried to manually set the type in the "SQL Types" tab of the database writer to "date," but I receive the exactly same error.

thank you

4

Hi,

Have you tried converting the date column to a string in KNIME that matches the format expected by Postgres?

Cheers,

Roland

5

Hello,

The format expected by Postgres is "date," but the closest I can get right now is "local date."

Is there a way to convert the column format to just "date" in Knime?

thank you

6

Hi Roland,

Do you have any addition feedback for this question?

thank you

7

Yes, I encountered the same problem: “Column “loading_date” of type “Local Date” from input does not match type “DATE” in database at position 2”, my KNIME version is 3.6.1.
I need to write a local date column to oracle db table, and the date format of the corresponding column in the table is “DATE”. Is there any way to convert a local date format column to DATE format in KNIME so I can write it into my oracle table?

8

Hi Hawk326040,

I think you can try the followings:

  1. Convert your Local Date to Date & Time format with Node Date&Time to legacy Date&Time. Depending on the Database type you are using, my experience with MYSQL db (if this happens to what you are using) is that it likes the legacy date/time format that KNIME provides.

  2. After you insert and update all the records to database, you can, if you want to, bring additional Date/Time Transform nodes, such as Legacy Date&Time to Date&Time and Date&Time to String, to fix the legacy format to a much more standard format now we are adapted to.

Please see the attached workflow and config clips for reference.

Let me know if you still struggle.

knime_workflow
knime_workflow1146×542 90.4 KB
config_slide
config_slide1920×1080 542 KB

Eric

2 Likes
9

jmpguru,
Your solution works fine, thanks very much!