Select Analysis Whereclause is like date string variable

[AndrewL]

dayString is a string that will change frequently.

I would like to use python to select records where my field EXP_CST (date field) in my feature class is on the same day as the date string. However, the date field will also have time elements, so I believe I will need to use the LIKE operator? I am using ArcGIS 10.5.1. Thank you.

dayString = '2018-01-31'

arcpy.Select_analysis(Select2, Select3, "EXP_CST LIKE '" + dayString + "'")

[TedKowal]

Andrew,

Dates are handled differently depending on what database you are using?  Are you querying a shapefile, file geodatabase, personal geodatabase, sql server, postgis, oracle ....   all these have different formats to address when dealing with date data.  

Please give the database type from which you want to apply the where clasue to, this will help folks properly answer you questions.  Each of the different databases has there own unique way of handling "Time" -- Generally speaking, using format statements are a way of striping off the time portion, also converting to Month, day and year to make your comparisons is another.  At first glance, it seems that you could search for your date directly... it is not appearent from your question if you are working with true dates (you mentioned that EXP_CST is a date field then in your are comparing a string with a date -- this will not work with all databases to it is important to mention what database you are working with.

Look here under dates and time to see how to covert string dates to dates ....for the various types databases.

SQL reference for query expressions used in ArcGIS—ArcGIS Pro | ArcGIS Desktop 

[AndrewL]

Thank you I am using a file geodatabase.

[AndrewL · Accepted solution]

From the link you sent I used the EXTRACT(extract_field FROM extract_source). I am not sure the LIKE operator is functional with a date field. I kept getting errors. 

However, using EXTRACT seems to work. My variables are strings.

arcpy.Select_analysis(Select2, Select3, "EXTRACT(DAY FROM EXP_CST) = "+dayDay+" AND EXTRACT(MONTH FROM EXP_CST) = "+dayMonth+" AND EXTRACT(YEAR FROM EXP_CST) = "+dayYear+"")

[TedKowal]

Yes that works, however as a tip.... I would also test and deal with "NULL" values for the EXP_CST string.  Null values seem to ocasionally pop up and bite you when you least expect them.

NOT "EXP_CST" IS NULL and EXTRACT(...)

Your Extract, I believe might fail with a null value.

Good Work!

[LotharUlferts]

This may help:

import arcpy as ap

table = <path_to_table>
dest_table = <path_for_your_output>
field_name ='EXP_CST'
day_string = '2018-01-31'

# If your string sequence is marked by double quotes, single quotes can be simply used  inside the string.
# The two '%' represents any number of signs (including none) before and after the string you're looking for.
# Use ap.AddFieldDelimiters to be flexible in shp, mdb, fgdb and others dataformats.
sql_expression = "{field} LIKE '%{sub_string}%'".format(field=ap.AddFieldDelimiters(table,
                                                                                                                                        field_name),
                                                                                           sub_string=day_string)
sql_expression #prompt the result
ap.analysis.Select(table, dest_table, sql_expression)

This code doesn't include a solution for transforming string to date and maybe reverse.

[TedKowal]

Nice tip on using AddFieldDelimiters...... have to remember that!  Generally I have always used brute force in shaping the data prior to the SQL call.