Beware that you cannot filter on Timestamps ! 8.4 b2710

If you have questions or if you want to share your opinion about Aware IM post your message on this forum
Post Reply
Jaymer
Posts: 2430
Joined: Tue Jan 13, 2015 10:58 am
Location: Tampa, FL
Contact:

Beware that you cannot filter on Timestamps ! 8.4 b2710

Post by Jaymer »

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 3139 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 3139 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.
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.

Jaymer
Aware Programming & Consulting - Tampa FL
Jaymer
Posts: 2430
Joined: Tue Jan 13, 2015 10:58 am
Location: Tampa, FL
Contact:

Re: Cant filter on a simple date. Internal SQL is formed wro

Post by Jaymer »

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 3136 times
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.

Jaymer
Aware Programming & Consulting - Tampa FL
Jaymer
Posts: 2430
Joined: Tue Jan 13, 2015 10:58 am
Location: Tampa, FL
Contact:

Re: Cant filter on a simple date. Internal SQL is formed wro

Post by Jaymer »

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 3136 times
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.

Jaymer
Aware Programming & Consulting - Tampa FL
Jaymer
Posts: 2430
Joined: Tue Jan 13, 2015 10:58 am
Location: Tampa, FL
Contact:

runtime error msg

Post by Jaymer »

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 3135 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.
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.

Jaymer
Aware Programming & Consulting - Tampa FL
Post Reply