Concatenate Date Time in SelectByAttribute

11-26-2021 05:26 AM
Occasional Contributor II


I need to concatenate Data in Python 3, the script below is in VB.

The PLANTIO field is with Date type and SAFRA text, but validation needs to be like the expression below. I've already tried transforming in ArcPRO Modelbuilder but without success.

Ano1 and Ano2 = Text

How can I do this?


arcpy.SelectLayerByAttribute_management("Censo_view1", "NEW_SELECTION", "!SAFRA! = '" + Safra2 + "' AND !PLANTIO! >= #04-01-" + ano + " 00:00:00# AND !PLANTIO! <= #03-31-" + ano1 + " 00:00:00#")

Tags (3)
0 Kudos
1 Reply
Regular Contributor

Your best bet is probably creating datetime objects from the strings and letting SQL handle the date comparisons. Check the documentation for formatting sql queries that include dates, can't guarantee this example will work but should give you an idea how to do this.  Also check where how you have your >= signs, it looks like it wants everything after 4/1 and everything before 3/31.


Safra2 = 'value'
anodte = datetime.datetime.strptime(f'04-01-{ano} 00:00:00', '%m-%d-%Y %H:%M:%S')
anodte1 = datetime.datetime.strptime(f'03-31-{ano1} 00:00:00', '%m-%d-%Y %H:%M:%S')

sql = f"SAFRA = '{Safra2}' AND PLANTIO >= {anodte1} AND PLANTIO <= {anodte}"

# or use SQL BETWEEN
sql = f"SAFRA = '{Safra2}' AND PLANTIO BETWEEN {anodte1} AND {anodte}"

arcpy.SelectLayerByAttribute_management("Censo_view1", "NEW_SELECTION", sql)