0

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.

  • 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 Aug 10 '16 at 13:53
  • 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. – GeoJohn Aug 10 '16 at 13:57
  • 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! – ed.hank Aug 10 '16 at 14:16
  • Datetime is not a string; it just looks like one. You said yourself, "I have a datetime field". – Tom Aug 10 '16 at 15:02
  • 1
    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. – GeoJohn Aug 10 '16 at 16:22
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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