0

In ModelBuilder I'm trying to create a table from a subset of dates in a Personal geodatabase table. I'm using "Make Table view" tool.

The date field in the table is [DateCreated]. I have two model parameter variables %Start Date% and %End Date%, both with data type set as date. I would like to return values from the table between these two dates.

I'm struggling to find resources to describe the required syntax. I'm also not very familiar with SQL.

So far I've tried the below with no success

[DateCreated] >  #%Start Date%# AND [DateCreated] < #%End Date%#

[DateCreated] >  %Start Date% AND [DateCreated] < %End Date%

[DateCreated] >  '%Start Date%' AND [DateCreated] < '%End Date%'

In all of these example the tool returns all rows

1

EDIT for Personal Geodatabase:

Try BETWEEN:

[DateCreated] BETWEEN #%Start Date%# AND #%End Date%#

I am using date format dd/mm/yyyy however I did notice that in my personal geodatabase this query reversed it to mm/dd/yyyy so gave me a different result than expected!


For File Geodatabase

Try BETWEEN, and the word date before each date parameter:

[DateCreated] BETWEEN date '%Start Date%' AND date '%End Date%'

See SQL reference for query expressions used in ArcGIS

When constructing an expression using dates in the expression builder, just select an existing date value and see what it gives you. You'll probably see something like DateCreated = date '12/09/2016' - you can then modify this expression to suit, by replacing the date between the quotes to reference your parameter, e.g. DateCreated = date '%Start Date%', and then proceed from there to build the rest.

Note that when I built the above expression, once I included the parameter reference the Verify Expression button returned an error, however when I ran the model it produced the expected output.

  • Sorry I just noticed you said Personal Geodatabase, not File geodatabase. I'll update as this will be different – Midavalo Sep 12 '16 at 5:49
  • I have updated for personal geodatabase - this works for me, and again the Verify Expression returns an error, but the model still runs without error. See my note though about how it seems to be reversing my day and month values in the query. – Midavalo Sep 12 '16 at 5:59
  • I tried the above for personal geo-database without success. I'm using the "copy row" tool to save the output from the "Make Table View" tool. When i look in the output table it has all records. Should i be using a different method/tools? How can i overcome the month to day switch? – Trapper Sep 21 '16 at 6:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.