Publishing a GP service with a date variable issue.

Question

I have a mxd with a sewer feature class and a  sql table which I joined.  Using modelbuilder I did a  SelectLayerByAttribute where by the user input  two dates, as part of the modelbuilder parameters, which work correctly within model builder when run. The date  field is with the table (not on the Sewer layer). When the tool is run from the toolbox is is also working correctly.   Yet when I published and then try to run the tool from the server I get a invalid SQL error message ( see sql1.jpg)

The expression below is what is used by SelectLayerByAttribute tools. I am thinking that sql does not like the "date" that is required to run the selection in modelbuilder.

TL_DT_INSP >= date '%Start Date%'   and TL_DT_INSP <= date '%End Date%'   and gbagis.GBA.SWNETG.OBJECTID > 0

Any clues as to how I might publish a tool without the "date"?

Thanks

sql1.jpg

Label Class valided ok.png

PaulLohr · Answer

I am trying to use a date variable with select by attributes against a shapefile and am having a similar problem. Modelbuilder's date type variable reformats the needed date format that I type in...yyyy-mm-dd to mm/dd/yyyy. Of course this mm/dd/yyyy format does not work with a shapefile. The documentation states that SQL used against a date column in a shapefile needs to be formatted as date 'yyyy-mm-dd'.

Working with ArcGIS Desktop 10.01, service pack 5.

_______________

Please bring back the old arcgis forums.

Katherine_Clark_0-1650651213206.png

MarceloCatalo_0-1650652058909.png

StephanieWendel · Answer

Hi Angel,

You may want to try either the extract or datepart as part of your expression instead. Check out Article 40088 - HowTo:  Search for specific parts of a date stored in a Date-Time field

It is possible that this is failing because you don't include time information. You can use the information outlined in the article to pick the parts of the date/time that you are looking to select on. Also, check out the date section of the SQL reference for query expressions used in ArcGIS to see if that also has the information that might help you get this right for processing.

I hope this helps!