Using a Date in an Arcade Filter Function

Question

I am using an Arcade Filter function in an attribute expression.  I am trying to pull back all records that were created today.  The sqlExpression looks like this:

var nestSurveySql = "NestGlobalID = '" + nestRec.GlobalID + "' AND survey_date > '" + Today() + "'";

The output to the console from this sql string looks like this:

NestGlobalID = '2e6b7d-0586-44a8-9e54-bf1f01e3' AND survey_date > '2022-06-21T00:00:00-07:00'

This seems to work as it does return records.  However, it is returning two records when I was only expecting one.  The extra record has a date that is from the day before today:

2022-06-20T17:55:33.196-07:00
2022-06-21T08:37:23.667-07:00

Is there a way to format this sql expression so that it works properly and only returns records for today?

TomFlahive1 · Answer

Hi Josh,

Thanks for providing this alternate solution.  Sorry it took so long to respond.  I was waiting to hear back from Esri on a support ticket related to this before I responded. You are right that it was a time zone issue, so we just republished the data and specified the time zone to take care of the issue.  I did try using the Extract function in my SQL statement, but I couldn't get it to work, even when I stripped my SQL statement down to using just a single EXTRACT function.  I was getting an error, but the error was very generic so wasn't much help in troubleshooting. But we are good now. Just wanted to thank you for responding.

jcarlson · Answer

I would guess that this is happening because of time zone differences between your machine and the server. If you're really looking for anything with today's date, you can also try to use EXTRACT in your SQL. Instead of using ">", we could check if the year/month/date match. Maybe a bit clunkier, but it ought to account for differences in time.

Also, rather than concatenating strings for you statement, you can use template literals to pipe things into the string. I find them easier to make sense of than juggling lots of separate strings. It also respects line breaks, so you can keep your SQL nicely formatted, if that's something you want to do.

var nestSurveySql = `NestGlobalID = ${nestRec.GlobalID} AND
EXTRACT(YEAR FROM survey_date) = EXTRACT(YEAR FROM '${Today()}') AND
EXTRACT(MONTH FROM survey_date) = EXTRACT(MONTH FROM '${Today()}') AND
EXTRACT(DAY FROM survey_date) = EXTRACT(DAY FROM '${Today()}')
`