I’m using DB Update en DB Input nodes to maintain tables. These tables have begin and end columns (both datetime) to keep track what’s applicable when.
I’m updating the enddate when the validity of a record ends using the DB Update node. In the WHERE part I specify the primary key of the table in combination with the begindate as “include” and these columns are specified as “exclude” on the SET part.
This works fine when the begindate has time 00:00, but when it is a real time, it simply refuses to update the records. Below you see the (part of) the output of the DB Update, together with the added status (and error - but these are all empty).
Every update fails if the time is other than 00:00.
I’m using KNIME 5.4.4 on Windows11 and our database is MS SQL Server 19. The output type mapping is default, i.e.
Does anyone know a work-around how to get these updates working again?
Hi @JanDuo , I don’t have the ability right now to test this with MS SQL but my suspicion is that the ones that fail do so not because of it not being 00:00 but because they contain milliseconds. Did you have a different mechanism for creating the non-midnight rows? If so, you might want to check back on where the milliseconds came to be included.
To be of greater help, it would be useful to know what is the column definition for those columns on the MS SQL database, as this would tell us more about what the database is expecting.
If my thoughts on milliseconds are correct, and you are not wanting the milliseconds in the database, I would suggest trying the following:
Prior to the db update, include a Date&Time to String node that converts the date using the format mask: yyyy-MM-dd'T'HH:mm:ss
which will strip of any milliseconds from the date and return a string
followed by a String to Date&Time with the format mask: yyyy-MM-dd'T'HH:mm[:ss]
which will convert the cleaned string back to a Date&Time column. Then see if it now updates.
Hi @takbb , Hi Brian,
The type of the columns in MS SQL Server is datetime. Depending on the tool they show up with milliseconds or even microseconds (where the decimals 4-6 always show up as 000).
Whether that’s a tool-default or not … not sure (been tricked too often on this ).
I’m using the getdate() function in SQL (DB Query Reader) to retrieve the current system date-time. This systemdate is used for both ending existing records (with an enddate - through the update) and inserting new records with a fresh begindate.
This means I have everywhere in my workflow columns of type Local Date Time, so I never thought I would need a String to Date&Time or Date&Time to String.
Since the problem I found started from the second update (of the same primary key).
I made an example workflow which produces the issue.
It is a mystery to me at the moment why the fractional seconds was causing the problem, if that is what is showing on the database, but without a database of my own to play with I can only guess.
Anyway, glad you have managed to get the update working and thanks for marking the solution
@takbb it’s even more mysterious: I ran the first workflow (without your trick) for the firsttime and it work as well. By coincidence the systemdate had a zero for the milliseconds (the seconds were something like 12.340).
I couldn’t believe my eyes, ran it again, with the shown result. It is really on the milliseconds where things go wrong. The tenth and hundredth of a second are not the issue.
As the update frequency of my tables is low, I would not even mind skipping the time completely. But your trick is nicer
).
