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:
SELECT
Date_Time AS date1,
Date_Time AS date2,
FROM Test_Data
WHERE Date_Time = :Date_Time
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