If you have questions or if you want to share your opinion about Aware IM post your message on this forum
#52687 by Jaymer
Thu Jan 09, 2020 3:53 am
Cant filter on a Timestamp. Internal SQL is formed wrong.

Simple date attribute (actually, its a Timestamp) in a grid.
Used the Column Options (the 3 dots) to filter "is after 10/1/19" . (picked October 1 2019 on popup calendar)
But the data find is for records AFTER Jan-10-2019
Screen Shot 2020-01-08 at 10.45.08 PM.png
Screen Shot 2020-01-08 at 10.45.08 PM.png (18.48 KiB) Viewed 1098 times


Here's what the logger shows and the SQL it is sending:
Screen Shot 2020-01-08 at 10.44.34 PM.png
Screen Shot 2020-01-08 at 10.44.34 PM.png (5.72 KiB) Viewed 1098 times


The end user is pretty confused cause the cool new tool can't filter on dates correctly.
Did I set something up wrong?

This SQL query finds correct records:
Code: Select all  select DateCreated from RO
  where DateCreated > N'10/1/2019'


so why is Aware switching the MM/dd ?
My format in the attribute is MM/dd/yy hh:mm
And in the grid, no one cares about the hh:mm part, so I had overridden grid display to MM/dd/yy
Last edited by Jaymer on Thu Jan 09, 2020 4:51 am, edited 2 times in total.
#52688 by Jaymer
Thu Jan 09, 2020 4:23 am
when i run this in the CRM, the SQL (from the logger) is different... but it works.
You can see its using the YYYY-MM-DD ISO 8601 format.
(don't know why though)
Screen Shot 2020-01-08 at 11.19.59 PM.png
Screen Shot 2020-01-08 at 11.19.59 PM.png (6.43 KiB) Viewed 1095 times
#52689 by Jaymer
Thu Jan 09, 2020 4:34 am
ok, the customer DOB is a plain Date field.
TipLastShown is a Timestamp.

THIS IS APPARENTLY THE PROBLEM/BUG
I mean, I knew that filtering on a timestamp field was already a pain - because if you manually enter a date or use the picker in the Filter line, then you have not added a "time" portion, so you'll never match a record that contains hours & minutes.

But you can see from the logger that now the Query is not formed correctly and our search fails.
Screen Shot 2020-01-08 at 11.31.25 PM.png
Screen Shot 2020-01-08 at 11.31.25 PM.png (6.59 KiB) Viewed 1095 times
#52690 by Jaymer
Thu Jan 09, 2020 4:48 am
also, if you try to filter on a DAY value > 12 (remember, its transposing Days & Months), you'll get an error to the user:
Screen Shot 2020-01-08 at 11.44.55 PM.png
Screen Shot 2020-01-08 at 11.44.55 PM.png (13.28 KiB) Viewed 1094 times


So, for my example above, filter was for "is after 10/1/2019", which doesn't find the correct records, but doesn't crap out either.
If you'd filter for "is after 10/15/2019" then since there are not 15 months, its an invalid date and the user gets this weird message.

Who is online

Users browsing this forum: No registered users and 15 guests