Query Date in Model Builder with Inline Variable

526
1
06-28-2020 10:25 AM
NealBanerjee
Occasional Contributor

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

1 Reply
LindsayRaabe_FPCWA
Occasional Contributor III

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

Lindsay Raabe
Forest Products Commission WA
0 Kudos