Using select by attribute query?

Question

Using Arc Desktop 10.3.1 I have a datetime field that is in the format of MM/DD/YY HH:MM:SS Some of the records have a only a date and some have both a time and a date. I want to strip off the time part using .split(" ")[0] but I am having difficulty figuring out how to craft the query to only select the records that have a time part. I have tried:

select * from !DateTime!.split(" ")[1] > 0 

The logic being I want to select all records from DateTime where the trailing time is greater than 0, then after they are selected I will run the field calculator to strip the time.

• GeoJohn: I'm thinking maybe you might have a casting issue. I think what is being returned by !DateTIme!.split(" ")[1] is probably a string and your boolean is evaluating if the string is greater than 0. Try casting the returned value as a quantative type. float(!DateTime!.split(" ")[1]). I'm not sure that this is the problem hence the comment as opposed to answer, but give it a shot.
• GeoJohn: Another thought... This will probably fail for records that don't split at (" "), because the [1] index will be a null reference. You could add a try/except in there. This would add some processing time, but depending on the number of records you are querying could be negligible.
• ed.hank: The first query of yours failed as well. I am not super familiar with the try/except so let me do some research on it and give it a shot. Thanks for the help!
• Tom: Datetime is not a string; it just looks like one. You said yourself, "I have a datetime field".
• GeoJohn: You will probably have to add a new field called "Time_Only" and field calculate it based on your successful splitting of the datetime object and then select by attributes on that field.

Answer 1

I have a python solution for you. This will create two layers, one with records with time, and one with records without time:

#feature class to be queried
fc = r'Path to your feature class'
#empty lists for the IDs
IDList = []
StripList = []
#search cursor to grab ID of records with Date and Time - change field names to match your fields
with arcpy.da.SearchCursor(fc,["Date","Unique_ID"]) as cur:
 for row in cur:
 Date = len(str(row[0]))
 ID = row[1]
 #records with time
 if Date>10:
 IDList.append("'{}'".format(row[1]))
 #records without time
 if Date<10:
 StripList.append("'{}'".format(row[1]))
del cur
IDs = ",".join(IDList)
#Query to select records with time
IDQuery = "Unique_ID In (" + IDs + ")"
arcpy.MakeFeatureLayer_management(fc,"ID_lyr")
#layer with records with time - ID_lyr
ID_lyr = arcpy.mapping.Layer("ID_lyr")
ID_lyr.definitionQuery = IDQuery
Strippers = ",".join(StripList)
#Query to select records without time
StripQuery = "Unique_ID In (" + Strippers + ")"
arcpy.MakeFeatureLayer_management(fc,"Strip_lyr")
#layer with records without time - Strip_lyr
Strip_lyr = arcpy.mapping.Layer("Strip_lyr")
Strip_lyr.definitionQuery = StripQuery