1

I want to select attributes by date, but ignore the minutes, seconds, etc. I've commented out some of the lines below and am just experimenting with hard-coded values.

The arcpy.management.MakeFeatureLayer() line worked with one shapefile that I'd imported from QGIS into ArcGIS Pro, where I am now working. The Date column in this file was a "TEXT" type column, and the date format was yyyy/mm/dd. I altered the format of the date in my SQL statement to accommodate a layer that I built in ArcGIS Online (data entered via the Field Maps app), but this line now kicks out an "ERROR 160195: An invalid SQL statement was used" message. I'm wondering if this has something to do with the Date column now being a "DATE" type column.

Based on this assumption, my workaround was to create a column called Date2, give it the data type "TEXT," and try to copy the info over from the original Date column - then see if arcpy.management.MakeFeatureLayer() would work with the Date2 column. However, this approach is kicking out this error: "ERROR 160327: A column was specified that does not exist." The Date column does exist (no space in the column title or anything).

I also tried arcpy.management.AlterField() at one point to alter the original Date field’s data type, but I got the "column does not exist" error there as well.

I need this script to work with the data that will be coming in via the Field Maps app. Can anyone recommend an approach that would work?

import arcpy
import datetime
from datetime import date

# set input layer & date
# inputLayer = arcpy.GetParameterAsText(0)
# inputDate = arcpy.GetParameterAsText(1)
# output = arcpy.GetParameterAsText(2)

inputLayer = 'Daily Reports Test'
inputDate = '5/13/2023'
# output = arcpy.GetParameterAsText(2)

arcpy.management.CalculateField(inputLayer, 'Date2', 'Date')

#inputDate formatting
inputDateTwo = datetime.datetime.strptime(inputDate, "%m/%d/%Y")
inputDatePlusDay = inputDateTwo + datetime.timedelta(days=1)

# convert date to hyphen format for use in output file names
tempDateTwo = datetime.datetime.strptime(inputDate, "%m/%d/%Y").strftime('%m-%d-%Y')

#set project and map variables
aprx = arcpy.mp.ArcGISProject('CURRENT')
m = aprx.listMaps('*')[0]

# selects by expression and creates a new layer from selected dates in inputLayer   
newLayer = arcpy.management.MakeFeatureLayer(inputLayer, tempDateTwo, 'Date >= \''+format(inputDateTwo.strftime("%#m/%d/%Y"))+'\' AND Date < \''+format(inputDatePlusDay.strftime("%#m/%d/%Y"))+'\'').getOutput(0)
m.addLayer(newLayer)

If this helps, when I print the SQL statements, they appear like this:

original script, working with the imported QGIS file:

 Date >= '2023/05/13' AND Date < '2023/05/14'

Updated script, working with the ArcGIS Pro file:

 Date >= '5/13/2023' AND Date < '5/14/2023'
2
  • 2
    Yes, dates are tricky, have a read of desktop.arcgis.com/en/arcmap/latest/map/working-with-layers/… when querying a date field in a shapefile preceed the literal date string with date, for example "Datefield" = date 'yyyy-mm-dd' if that doesn't sort it out populate a field of type long integer with int(YYYYmmdd) (python syntax), you should be able to query a range properly with this numeric representation of date. Commented Jun 15, 2023 at 1:09
  • 5
    Shapefile doesn't even support time in dates. dBase does define (amazingly, in the pre-Y2K era) dates as YYYYMMDD. I'd recommend using ISO formatting (date 'YYYY-MM-DD')
    – Vince
    Commented Jun 15, 2023 at 3:12

1 Answer 1

0

Turns out the issue was caused by something really simple: the field I listed in my SQL expression. I was using the Display Name for the field, not the actual field name - Date vs DateTaken. Leaving this up in case it helps someone.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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