Query Date in Model Builder with Inline Variable

[NealBanerjee]

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

[LindsayRaabe_FPCWA]

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

[aaronkrusniak]

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

Aaron Krusniak
Chicago Department of Public Health

[BrendanFordDC]

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

[Psoralea]

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