When I bind this named query to the data property of my table in perspective the date comes back as a very large number instead of something readable, in my database its a data type "date" and displays correctly.. any ideas?
That's an epoch milliseconds value. Do you have an entry in the columns array for your Date table, and what is it set to treat your column as?
Within SQL or Ignition? Sorry I don't understand the question.
In your perspective table component, find the columns property under props. Add entries to it for each column in your dataset (as needed). The first element in each column object should be a key field with a value that exactly matches the column name of your dataset.
Then, further down in the column object corresponding to your DATE field, change the 'render' property to 'date', and see if that fixes your issue.
Like this
1 Like
So while I've got you're attention...
I am trying to filter only on the date selected by this date input:
this works for saving when I run my save named query but it does not seem to work when using that same value to filter:
"Does not work" as in returns no data? Or is there any kind of error?
If the former: Is the tag of the correct type? Does it work if you pass the parameter wrapped in the toDate expression function (as in, toDate({path/to/tag}))
Sorry, does not work as in returns no data:
I use the same date input (tag value) to provide the value for the save, and it works.
Try changing your query where clause to this so it matches on just the date and not the date and time.
date(DATE) = date(:myPB1AccidentDate)
com.microsoft.sqlserver.jdbc.SQLServerException: 'date' is not a recognized built-in function name.
MSSQL is why, you'll have to use the correct date function, which I think is DATEPART(). I don't know the correct syntax for using it.
IIRC your WHERE clause should be something like so:
WHERE CAST([Date] AS date) = CAST(:myPB1AccidentDate AS date)
Also, wrap DATE in your SELECT with []:
SELECT [Date]
actually you might not need to wrap it with []. It worked in SSMS for me without wrapping it
2 Likes
I tried it both ways, wrapping [DATE] in the select and not, still not returning any data...
is it giving you any errors?
I have one error in the property editor but it has been there for a while, even when the select * was working showing the correct date format in the table. I'm unsure how to see what that error is though..
it looks like the query is succeeding, just not returning any data for the selected date.
do you have data in the database table that have a date matching what you're passing into :myPB1AccidentDate?
2 Likes
i may or may not have had a date selected with no entries...
1 Like
