Problem appending time column to database

database
#1

I am having a similar problem. I would like to append a SQL table from an excel spreadsheet and come across a variety of errors. I have one field that is a date and another that holds time. When I added the modify time node to remove the time from the date - that error was resolved, but now I have an error with the time field that I haven’t been able to resolve.

“casetime” of type “Local Date Time” from input does not match type “time” in database at position 2

When I use String to Date/Dime:
ERROR String to Date/Time (legacy) 0:14 Execute failed: Maximum number of fails reached: 100

I have tried to use nodes; Modify Time, Modfiy time zone, Time Field Extractor, and Extract Time Window(Legacy) and no desired result.

Any help is greatly appreciated.

Thanks!
Noemi

0 Likes

Feature Request: String to Date&Time should be able to extract date from date&time string
#2

Hi there Noemi,

You are seeing this error when trying to write data to database? If so it seems to me you have a time type in database and Date Time type in KNIME. Convert Date Time to Time using Modify Time node and then it should work.

If I got something wrong can you please add more information and same sample data?

Br,
Ivan

0 Likes

#3

Hi Ivan,

Yes, you are correct I am trying to append to an already existing table. Both the dataset and the table have a date field and a time field. When I import the data to KNIME, the time field becomes a Local Date Time so I used Modify Date Node to remove the date from the casetime column. Now, when I run that I get this error message:

Execute failed: java.lang.RuntimeException: Column “casetime” of type “Local Time” from input does not match type “time” in database at position 2

So I can see that I need to modify the format of that time. When I tried the Extract Date&Time
I get this error: Execute failed: java.lang.RuntimeException: No. of columns in input table > in database; not existing columns: [hour, minute, second, “subsecond (in milliseconds)”]

The format for CaseTime in my database is: 00:00:00.0000000
I can’t seem to find a node that will create this as one field. Is there another node you can recommend?

As a workaround - I have tried to create a new table, rather than append. When I try to use the new table to union and insert both sets into a new table, I get errors like this:
Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.
and this is after using cast and tryparse.

Gracias,
Noemi

0 Likes

#4

Hi Noemi,

I moved this issue to new topic as it is not so related to the one in which you posted and to be more visible :wink:

Couple of questions. What KNIME version are you using? Are you using new database nodes? What is the database you are working with?

Br,
Ivan

0 Likes

#5

Hi Ivan,
The database node I am using is Database Writer, and I am not certain whether or not it is new.

Here’s how I am set up:
The database I am working with is
Microsoft SQL Server 2017 (64 bit version)
Microsoft SQL Server Management Studio 17
Microsoft JDBC Driver 7.0 for SQL Server
Microsoft JDBC Driver 6.4 for SQL Server
Microsoft JDBC Driver 6.0 for SQL Server
(I installed all 3 of these because nothing seemed to work until they were all in place)
KNIME 3.7.2 (64 bit version)

I hope this helps.

Thanks,
Noemi

0 Likes

#6

Hi Noemi,

Couple of comments:

  • you do not need to add any driver for Microsoft SQL. By default KNIME is using open source jTDS driver. If you want the official one you can install it like any other Extension. Check here for more: DB reader Error Data Truncation
  • You can try the new database integration nodes cause you are using to old ones (legacy) where mapping is improved. New integration also offers more features and new functionalities. If you update to KNIME version 4.0.0. you will have them by default. Otherwise you would need to install them as well.

So if I were you I would update KNIME and try new Database Integration nodes. If you do not update you can still try them. In case of not updating KNIME I would also remove all three drivers you added and install official one. If you don’t want to try nothing of above mentioned you can try converting your column “casetime” of type “Local Time” with Date&Time to legacy Date&Time node to get the old format for Time type column. Maybe it will work with it.

Anyways here is link to both old and new database integration guides which you might find useful in your work :wink:

Br,
Ivan

0 Likes

#7

Hi Ivan, I updated and tried everything again - only to fail.
I created an excel reader, added nodes: modify time, modify date, then I used Database Writer (legacy) and it ran, but the records never went to the database. After spending a full day on this I am taking a break for the evening and coming back to it tomorrow.

In sum, I have had to work through JDBC connection issues, data type conversions, and failed executions when running an update to a SQL table.

It seems like it could be straightforward: grab excel file, use to append SQL table. I searched for a workflow I could follow as an example, but was not able to find it.

0 Likes

#8

Hi there,

to be honest not sure what steps have you taken at the end :slight_smile:

Anyways here is link to Database Writer (legacy) node. There you can find example workflows were it was used.

Br,
Ivan

0 Likes