Port Old Python to python 3 expression help

[RaymondSeth_III1]

I am trying to port this code to python 3 and am having a terrible time trying to recreate this expression. I am really new to python but more specifically these expressions. the varible dateStart and dateEnd are int as well as Num in the table being a LONG

expression = "mid(trim(str([Num])), 5, 4) not between '" + dateStart + "' and '" + dateEnd + "'"

arcpy.management.SelectLayerByAttribute("workLyr", "NEW_SELECTION", expression)

[RaymondSeth_III1]

how would I write that if Num is a table attribute?

[RhettZufelt]

So you are trying to convert old VBA code to python code to genereate an SQL expression to feed into the select by attributes tool.

You say they are int/long fields and not date.

Do the date fields have just day, just  month, days since 1/1/1901, etc.  since they are int, they must be a number and can't resemble "10/7/2022".  Also, what is the number field?  How many digits, etc.

Would be easier to troubleshoot if we have examples of what the data looks like rather than trying to guess what you are after.

Would also help if we knew "where" you are trying to do this.  Script tool, stand alone python, python in Pro, Notebook, etc.

R_

[RaymondSeth_III1]

It's old python code that works as is in ArcMap 10 as a script tool and I am trying to port it to work in pro. I have tried both running it as a script tool and working with just those lines of code in a notebook within the pro-environment for troubleshooting and I am getting the same errors doing it either way. The Num is a LONG datatype attribute in a table within a feature class that will have a number like 20221007 that represents a date, the start and end date are basically just 4 digit numbers from user input that would be in the format of 1007 basically an integer that contains the month and day. So the first part of that expression is trying to strip out just the month and day (last 4 digits) so it can be compared to see if it falls outside the range given by the user. 

[RhettZufelt]

Not sure how it was done in ArcMap, but don't really think this is possible.

from documentation: 

Here is a basic form of a SQL expression WHERE clause:

<Field_name> <Operator> <Value or String>

So, not sure how having a value for the <Field_name> would work as the expression evaluates row by row comaring a field value to some expression.

However, if you were to make a new column in the data, say Num2 and calculate that to:

Num2 = int(str(Num)[4:])

That will populate the Num2 field with the last 4 digits of the Num value:

>>> Num = 20221007
>>> Num2 = int(str(Num)[4:])
>>> Num2
1007

You would then have a "Field" with the value you want to compare with the expression.  Something like:

expression = f"Num2 BETWEEN {startDate} And {endDate}"
arcpy.management.SelectLayerByAttribute("workLyr", "NEW_SELECTION", expression)

This is working for me.

Suspect you could use cursors to append the matching OID's to a list and select by that.

Interesting to see if you find a solution as I'm not aware of how to use a variable for field input.

R_

[RaymondSeth_III1]

I am going to work on this approach I have been out of the office a couple days I will report back thanks for the for the recommendations.