DB Query Reader v4.3.4 Date converted to String?

[izaychik63]

Is it a bug? When I read Birthday from SQL Server in became local date&time.
If I use cast(Birthday as Date) as Birthdate it return as string. On the mapping tab Date mapped to Local Date.

[tobias.koetter]

Hi izaychick63,
this is strange but you never know with the date and time types. Can you please copy your query into the SQL Statement field of the DB Table Selector node using the Custom Query option. Execute the node and have a look at the DB Spec tab of the DB Data port of the node. The DB Type and DB Type Name columns show you which type the column has. If you need further help please attach a screenshot here or put the name and type into the comment.
Thanks
Tobias

[izaychik63]

Thank you, Tobias. DB Table Selector shows column as a string. I’m just wondering how SQL server knows that cast(Birth_date as Date) is a date and KNIME considers as a general type string? Possibly some internal flags on a field specification needs to be analyzed.

[bruno29a]

Hi @izaychik63 , not sure if that’s only for MSSQL or for 4.3.4, but I tested with mysql and H2 in 4.4.1, and it seems to be no problem.

My test workflow looks like this:

Source data is from Mysql and data looks like this:

We can see the date column as a date type, and the datetime column as a datetime type.

I’m running this query:

SELECT *
     , CAST(`datetime` AS DATE) AS datetime_date
FROM test.test_dates

You can see in the prevew results that the last column, which represents the cast of the datetime column as date, is of type date.

The results confirm it too:

For the H2 data, I re-create the original data only (so, I exclude the cast result):

Similarly as for the mysql one, the Query Reader for the H2 table shows the correct types:

We can see the original columns on the left, and in the Preview results, we can see that the column that was cast to date is of type date.

Results of the query:

I don’t have 4.3.4 anymore, nor do I have an MSSQL instance.