topic Re: Model Builder SQL Expression in ModelBuilder Questions

Model Builder SQL Expression

deleted-user-V6usQu8yPcp9 — Wed, 30 Mar 2016 21:12:20 GMT

I am trying to set up a tool to query based on a date range and secondary field

I have created three Variables that i have set up as Parameters, Start Date, End Date, Customer_Code.

I have the customer code selecting correctly however i get an error when i include my date range.

(Date >= '%Start Date%' AND Date <='%End Date%') AND Customer_Code = '%Customer Code%'

This is the error i get

NEW_SELECTION "Customer_Code = '35' AND Date => '3/1/2016 3:33:02 PM'"

Start Time: Wed Mar 30 16:06:22 2016

ERROR 000358: Invalid expression

An invalid SQL statement was used.

An invalid SQL statement was used. [Customers_3_24_16]

An invalid SQL statement was used. [SELECT OBJECTID FROM Customers_3_24_16 WHERE Customer_Code = '35' AND Date => '3/1/2016 3:33:02 PM']

Failed to execute (Select Layer By Attribute).

Failed at Wed Mar 30 16:06:22 2016 (Elapsed Time: 0.00 seconds)

I think the issue is that when i apply a definition query my date values look as like this

Date = date '2016-03-01 00:10:00'

Thanks in advance for your help!

Re: Model Builder SQL Expression

WesMiller — Wed, 30 Mar 2016 23:22:03 GMT

Dates are tough to work with, i suggest you use select by attribute to see the format the date query is looking for.

Re: Model Builder SQL Expression

deleted-user-V6usQu8yPcp9 — Thu, 31 Mar 2016 12:55:37 GMT

I have my SQL expression going connected to a select by attributes i think the issue is the "Date = date '2016-03-01 00:10:00'"

not sure if this is an issue but i have underlined and made bold the part that might be stopping it from working

Date = date '2016-03-01 00: 00:00'

Re: Model Builder SQL Expression

WesMiller — Thu, 31 Mar 2016 13:57:57 GMT

Check your error message I'm not sure you can use PM in the date field try using a date from a select by attribute Get Unique Values test

Date => '3/1/2016 3:33:02 PM'

Re: Model Builder SQL Expression

curtvprice — Wed, 06 Apr 2016 05:09:04 GMT

The biggest problem is that date-time formats are often format and database-specific. It looks in the help like Wes is correct that PM is not likely to work for most data sources.

SQL reference for query expressions used in ArcGIS—Help | ArcGIS for Desktop (Working with date and time)

The format you reference is correct for file geodatabase:

Datefield = date 'yyyy-mm-dd hh:mm:ss'