Date, Datetime2 data types from database is read as String

The columns with datatypes date and datetime2 are being read as string. The corresponding input type mapping indicates that date is being read as Local Date but doesn’t appear to be.

Just in case it’s vague, I’m trying to retrieve data through the DB Query Reader and date fields aren’t being retrieved as expected which is creating a few problems. Would somebody have an idea about this.?

@Vatsa welcome to the knime forum.

You could either try and change the field mappings:

https://docs.knime.com/latest/db_extension_guide/index.html#db_type_mapper

or you could convert the strings to date an time values in knime.

1 Like

Hello @mlauber71, thank you for the quick response. The workaround works, appreciate it. I was scouring for solutions and found a few others as well. So, I’ll to reframe the question. The database datatype to Knime datatype mappings are mentioned, which aren’t taking place as planned/defined right.? There is some issue in reading the database datatypes.

Not just that, I created a data time variable which is also being read as string. The traits are not being retained as is. Is this a caveat.?

@Vatsa could you give us more details? What kind of database. What types of variables. What is the code you are using. Maybe a screenshot of your settings of the mapping.

Sure. Below are the screenshots wherein I created the datetime timestamp which gets pulled in as a string. Do let me know if these help. In this case, my goal is to obtain the timestamp during the execution, do let me know if there’s a better way.


Pic3

Here is the screenshot from the database with the datatypes and the same in the joiner node.

Pic5
Pic6
Pic7

I’m new to the tool and open to suggestions. Feel free to point out any concerns.