Date column from SQL database

KNIME Analytics Platform
1

I am reading data in from one SQL database, this has a date field, which is coming through as Date and Time (2017-09-30T00:00:00.0).

I also am reading from a separate SQL table (with a second Database Reader node). This is reading a table with a field that is actually formatted as string ‘2017-09-30’.

I want to eventually join these two data tables together on this date field. I have read a lot of different posts about date conversion and modification nodes but I can’t seem to get any of them to work. The closest I seem to get is to Convert the latter from string to Date but after I do this, this field says it has column type = ‘Local Date’.

On the former, I have tried the Modify Date node but when I go to Configure this one, none of the columns feed into the node for selection.

2

I think I figured it out.

I ended up using ‘Mask Date/Time (legacy)’ node and ‘Legacy Date&Time to Date&Time’ node on the former. I was then able to join using the Joiner node.

Is there a simpler way to do this? I am surprised by the amount of steps I needed to take here.

3

I will use string manipulation to do it:
just take first 10 char from the 2017-09-30T00:00:00.0

if your string actual is “(2017-09-30T00:00:00.0)”, then you can use
substr(time,1,11 )

image
image872×624 28.5 KB

1 Like
4

if you need using sql to slove this problem. I just use postgresql.
you can use like this:

select ‘2010-01-01 12:00:00’::timestamp::date;

It will show 2010-01-01

5

thank you