Query Date in Model Builder with Inline Variable

NealBanerjee — Sun, 28 Jun 2020 17:25:11 GMT

Hello,

I have had challenges querying date field (in a file geodatabase) within Model Builder when passing a date value using inline variable substitution. After some frustration with many attempts, I figured out a very simple and effective solution that I thought I would share.

Situation

You have a model that gets a date value as input parameter (user entered, from an existing field in feature class, etc.) and store as inline variable (e.g. valDate)
You want to query a date field (e.g. 'CompletionDate') in existing feature class again the date passed by the inline variable date. For example you want query 'CompletionDate' > valDate in the 'MakeFeatureLayer' tool (see below)
You may try a simple syntax as shown below - THIS WILL NOT WORK

Issue

Even if the valDate inline variable is defined as a 'Date' is is passed as a string, so you would need to include the reference %valDate% in quotes as such '%valDate%'
However, that wouldnt fix issue since the date value passed as string is likely to be in mm/dd/yyyy format (e.g. 07/04/2020) but date format for file geodatabase in SQL query is "date '<date> <time>'" (date '2020-07-04)

Solution

Use the CAST function in your SQL query so that it "casts" (i.e. converts) string being passed from valDate to a Date value.
See example below - THIS SHOULD WORK

Hope you find this helpful

Neal

Re: Query Date in Model Builder with Inline Variable

LindsayRaabe_FPCWA — Fri, 23 Jul 2021 05:05:26 GMT

Thanks - a simple solution to an annoying problem. Worked a treat.

Re: Query Date in Model Builder with Inline Variable

aaronkrusniak — Wed, 08 Dec 2021 19:44:23 GMT

This saved me some major headaches. Thanks so much for sharing!!

Aaron Krusniak
Chicago Department of Public Health

Re: Query Date in Model Builder with Inline Variable

BrendanFordDC — Fri, 22 Jul 2022 18:36:14 GMT

This did the trick. Thanks for sharing. Will come in handy many times over.

Re: Query Date in Model Builder with Inline Variable

Psoralea — Thu, 16 Mar 2023 00:39:52 GMT

Thanks you so much for this Neal, really made my morning!

topic Re: Query Date in Model Builder with Inline Variable in ModelBuilder Questions

Query Date in Model Builder with Inline Variable

Re: Query Date in Model Builder with Inline Variable

Re: Query Date in Model Builder with Inline Variable

Re: Query Date in Model Builder with Inline Variable

Re: Query Date in Model Builder with Inline Variable