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
"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}))
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
I tried it both ways, wrapping [DATE] in the select and not, still not returning any data...
is it giving you any errors?
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?
i may or may not have had a date selected with no entries...
