topic Select Analysis Whereclause is like date string variable in Python Questions

Select Analysis Whereclause is like date string variable

AndrewL — Mon, 05 Feb 2018 15:28:57 GMT

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 + "'")

Re: Select Analysis Whereclause is like date string variable

TedKowal — Mon, 05 Feb 2018 15:57:56 GMT

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

Re: Select Analysis Whereclause is like date string variable

AndrewL — Mon, 05 Feb 2018 16:01:45 GMT

Thank you I am using a file geodatabase.

Re: Select Analysis Whereclause is like date string variable

LotharUlferts — Mon, 05 Feb 2018 16:48:19 GMT

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.

Re: Select Analysis Whereclause is like date string variable

AndrewL — Mon, 05 Feb 2018 19:23:57 GMT

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+"")

Re: Select Analysis Whereclause is like date string variable

TedKowal — Tue, 06 Feb 2018 15:41:09 GMT

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!

Re: Select Analysis Whereclause is like date string variable

TedKowal — Tue, 06 Feb 2018 16:10:34 GMT

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.