Compare t_stamp with datatime tag in query

Good morning guys,
in perspective I have a DateTime Input components; its value props is binded to a DateTime memory tag (format string = “yyyy-MM-dd h:mm:ss aa” as default).

I create a query in a query tag and I’m trying to compare the date of my DateTime memory tag with the t_stamp of the table, but it always return Error_ExpressionEval.

The query works because if I test it with this comparison everything is ok:

WHERE DATE(t_stamp) = DATE(NOW())

I try to compare it in this way but it’s not working:

WHERE DATE(t_stamp) = DATE({[.]Giorno selezionato consumi fasce orarie.value})

Curly braces in queries perform string conversion on the given object before inserting into the query text. The object at that point is just a value, disconnected from its source, and therefore uses the default conversion for the object. For datetime values, this is almost never compatible with a database’s expectations. For anything but numbers, you also need single quotes in the SQL just outside the curly braces.

The correct answer is to use a query type that take parameters, either ? parameters in “Prep”-style scripted queries, or :named parameters in named queries. These operations pass the value natively to the DB without string conversion. Note that an equals comparison is unlikely to work, as native Ignition date/time objects have millisecond precision.

If you absolutely must use curly braces, you will need an intermediate property to hold a deliberate string conversion of your datetime object, using the format your DB needs.

3 Likes

Thanks for the explanation e for your time @pturmel .

Actually I must not use curly braces, that’s just how I thought to make it work, but if is not correct I can change without problem.

Use a named query is not a problem, I started to use them with perspective, but I can’t understand how to use a it in a query tag, I can’t find any menu to select it.

Eventually I could use a runScript in an expression tag?

Thanks again

For the moment, runScript is the only way to run a named query for a query expression tag.

I tried this out and got it to work…

I formatted the date on the tag to be the below and that is bi-directonal binding with the date picker component.

image

Then my query is…

SELECT *
FROM dpr
WHERE DPRDate = '{[.]Date.value}'

Not ideal as it goes against Phils advice on using curly braces but with my limited knowledge this is all I’ve got. DateTime comparison with databases has always been a trial and error for me.

As Phil suggest if you expand the date and use the Time also you results will just about always be zero as the database time stamp is down to the millisecond.

Im prepared to be shot down for the incorrect way of doing things :grinning: But getting something working is a start…

1 Like

Thanks for the help, this works great!