Date Flow Variable converted to String not usable in SQL

[mschutterop]

I am trying to use date flow variables in the SQL for a DB Table Selector node. My flow variables are Date types on the output port of the Table Row to Variable node but are presented as strings in my DB Table Selector node. When using the two flow variable in the SQL I get "ERROR: Operator does not exist: date >+ integer. "

Is there a way to force my flow variables back into the Date type?

[morpheus]

Hi @mschutterop,
try to put the variable between apostrophes e.g. ‘$${SWeek Ending Date_Start}$$’.

BR

[elsamuel]

@mschutterop

My flow variables are Date types on the output port of the Table Row to Variable node

Are you sure about that? I didn’t think it was possible to create variables of the type “Date&Time” with any of the variable nodes.

[bruno29a]

Hi @mschutterop , you need to enclose the variables between single quotes.

You can see from your Parsed Statement what the query will turn out to be:

The correct statement should end up being:
where "TimeOffDate" between '2021-08-08' and '2021-08-13'

So, in order to reflect the above, you need to change your query to this:

SELECT * FROM "public"."Timeoff"
where "TimeOffDate"
between '$${SWeek Ending Date_Start}$$'
and '$${SWeek Ending Date}$$'

Also, the error message kind of give you a hint about the issue:

And between ... and ... statement is essentially treated as >= ..... <= .... and since 2021-08-08 is not enclosed in quotes, it’s actually being treated as a subtraction, and is literally calculating 2021-08-08 = 2005, so it’s trying to compare TimeOffDate, which is a date column, to the integer 2005, hence why the error message complains about “date >= integer”

[mschutterop]

OK. Very strange. That was the first thing I tried and it didn’t work, but this morning it does. Thank you for the magic from a distance.