Named Queries/DateTime

I am scratching my head at this, which returns no results even when I copy and paste the literal epoch date number into :Date_Time in the Named Query “testing” window.

SELECT Date_Time, :Date_Time
FROM Test_Data
WHERE Date_Time = :Date_Time

This should return two identical columns with the same Date_Time I selected. Is Ignition changing the data somehow? Or is there another angle I need to consider?

This may be something I’m missing, but can you explain :Date_Time in your SELECT statement? Usually you have the parameter in the WHERE clause…

By using :Date_Time in your SELECT statement you are essentially saying this (I’ll use today’s date as the input)

SELECT Date_Time, 12/7/2021
FROM Test_Data
WHERE Date_Time = 12/7/2021

To do what you want, just do something like the following:

   Date_Time AS date1,
   Date_Time AS date2,
FROM Test_Data
WHERE Date_Time = :Date_Time
1 Like

You made no mention of what results you got. (I suspect nothing returned).
EDIT: Sorry, reread you post, and you did say. Time for more coffee…

Most databases have datetime columns to millisecond precision. Consider truncating your Date_Time column to seconds in your query.

You will likely have to change this to fit your db.

SELECT TRUNC(Date_Time, 'SECOND'), :Date_Time
FROM Test_Data
WHERE TRUNC(Date_Time, 'SECOND') = :Date_Time

Ah, yes my database stores datetime to millisecond precision. So then, does Ignition strip that somewhere? I know that (for example) in a Perspective table I can actually show the date as the epoch number instead of human-readable text by changing the column render - and at that point milliseconds are still there.

Ignition generally does not strip milliseconds anywhere.

Phew! Figured it out. No millisecond truncation; I had to use DATEDIFF instead of = or LIKE in the WHERE clause:

SELECT Date_Time, :Date_Time
FROM Test_Data
WHERE DATEDIFF(second, Date_Time, :Date_Time) = 0