Join a Table to a Feature Layer with a Where Clause

[DNVT]

I am trying to calculate a field in the feature class from a table. The table and feature class have a common field for a join. I want to filter the feature class to only edit rows that have been edited in the past 7 days. I have created a feature layer with a Where Clause filtering all rows that have been edited in the past 7 days. I am unable to join the table to the feature layer. When I make the feature layer without the where clause the join works and I am able to calculate the field. With the Where Clause in Python I receive an "An invalid SQL statement was used" error. I have also tried each scenario in ArcMap. Without the where clause works but when joining with a feature layer with a Where Clause I receive an error in the join validation:

"The number of matching records for the join:
- Unexpected error encountered.
- No matches found by joining"

Has anyone else ran into this problem?

Python code with Where Clause

# Get start and end times for report
start_time= datetime.timedelta(days = 7)
end_time = datetime.datetime.now()
report_time = end_time-start_time #this is the time that gets used to filter records

# Find all records that are later than or = to report_time
arcpy.MakeFeatureLayer_management(lyr, FeatureLyr, dateSearch)

# Join the table to the feature layer and calculate field
arcpy.AddJoin_management (FeatureLyr, JoinField, JoinTable, JoinField)

arcpy.CalculateField_management(FeatureLyr, "TransferField", "!JoinTable.TransferField!", "PYTHON_9.3")

Python code without Where Clause

# Find all records
arcpy.MakeFeatureLayer_management(lyr, FeatureLyr)

# Join the table to the feature layer and calculate field
arcpy.AddJoin_management (FeatureLyr, JoinField, JoinTable, JoinField)

arcpy.CalculateField_management(FeatureLyr, "TransferField", "!JoinTable.TransferField!", "PYTHON_9.3")

[DNVT]

I am working in an ArcSDE environment. I have tried other Where Clauses other than a date and it works. When I use a date as my Where Clause I keep receiving the error:

ERROR 999999: Error executing function.

An invalid SQL statement was used.

An invalid SQL statement was used.

An invalid SQL statement was used.

An invalid SQL statement was used.

Failed to execute (CalculateField).

[RandyBurton]

In this line of code

arcpy.MakeFeatureLayer_management(lyr, FeatureLyr, dateSearch)‍‍‍‍

dateSearch looks to be a where clause, but I don't see where/how it is getting initialized.  Are you using something like:

# for file geodatabase
dateSearch = "dateField > date '{}'".format(datetime.datetime.strftime(report_time,"%Y-%m-%d %H:%M:%S"))

# result: "dateField = date '2018-01-21 19:01:29'"‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

The format of the where clause will vary depending on the database you are using. See SQL reference for query expressions used in ArcGIS.