topic Re: Why is this SQL expression invalid? in ArcGIS Pro Questions

Why is this SQL expression invalid?

ChrisCowin — Thu, 16 Dec 2021 23:39:25 GMT

I'm upgrading a model that processes GPS Points. From the GPS device we can only get a table with every point, so I'm trying to create additional function that will go to the ultimate GDB and get the last date in the field "Start_Time" which is a date field. I achieve this by sorting by descending of this field and then "Get Field Value" from utilities into a date variable. Where I'm stuck is making the SQL expression using inline replacement to delete all the values that are lower than the variable I got from the server. However everything I've tried returns an error saying the expression is invalid.

Expressions I've tried based off this article:

Start_Time >= date %Last_Start_Date%
"Start_Time" >= date %Last_Start_Date%
Start_Time >= %Last_Start_Date%
"Start_Time" >= %Last_Start_Date%
Start_Time >= /'%Last_Start_Date%/'

The value that the variable reports is:

2/12/2021 5:00:41 AM

I don't really know what else to try. Its really hard to understand why it doesn't work when the error just says it doesn't work.

Re: Why is this SQL expression invalid?

jcarlson — Fri, 17 Dec 2021 00:15:59 GMT

What DB system are you on? That article references a few other formats for things like Oracle, Postgres, etc.

Re: Why is this SQL expression invalid?

ChrisCowin — Fri, 17 Dec 2021 00:21:17 GMT

Just a regular old GDB, nothing fancy. I tried changing the variable to a string and then used:

Start_Time <= date '%Last_Start_Date%'

That seems to have worked, but think its a little odd I can't do that directly since its already in a date field.

Re: Why is this SQL expression invalid?

AYamaguchi_UGRWCD — Mon, 17 Nov 2025 20:04:29 GMT

Still having this problem in 2025. @esriesri Plz fix! I'd like be able to use a date parameter without needing the SQL editor.

Thanks so much @ChrisCowin for the workaround!

Specific use case was: In Model Builder, using the Select tool where one of the selection criteria was [DateColumn] >= [user-defined, single-value parameter]

The only thing I could think of that might cause issue was that:

[DateColumn] has the format 'dd-MMM-yy hh:mm:ss.sss',

[user-defined parameter] could only be entered as 'dd-MMM-yy hh:mm:ss' when (data type=date)

(the trailing milliseconds were omitted). Wondering if the discrepancy in data format is what caused the issue?