How can I write a definition query for feature classes to divide a larger table by year?

Question

My source data is a set of annual tables that have been merged into one. If I use these definition queries, only the first one works. Is there an error in my SQL statement? Status = 'Completed' AND Delivery_Date > date '2018-01-15 00:00:00'Status = 'Completed' AND Delivery_Date < date '2018-01-15 00:00:00' AND Delivery_Date > date '2017-01-15 00:00:00'Status = 'Completed' AND Delivery_Date < date '2017-01-15 00:00:00' AND Delivery_Date > date '2016-01-15 00:00:00'Status = 'Completed' AND Delivery_Date < date '2016-01-15 00:00:00' AND Delivery_Date > date '2015-01-15 00:00:00'Status = 'Completed' AND Delivery_Date < date '2015-01-15 00:00:00' AND Delivery_Date > date '2014-01-15 00:00:00'Status = 'Completed' AND Delivery_Date > date '2014-01-15 00:00:00' The data works fine as separate feature classes. My client requested I do it this way.

JayJohnson2 · Answer

Joshua, I will try that. My coworker Ryan did get it to work without between: Status = 'Completed' AND Delivery_Date <= CURRENT_DATE AND Delivery_Date >= CURRENT_DATE - 365Status = 'Completed' AND Delivery_Date <= CURRENT_DATE -365 AND Delivery_Date >= CURRENT_DATE -730Status = 'Completed' AND Delivery_Date <= CURRENT_DATE -730 AND Delivery_Date >= CURRENT_DATE -1095Status = 'Completed' AND Delivery_Date <= CURRENT_DATE -1095 AND Delivery_Date >= CURRENT_DATE -1460Status = 'Completed' AND Delivery_Date <= CURRENT_DATE -1460 AND Delivery_Date >= CURRENT_DATE -1825Status = 'Completed' AND Delivery_Date < CURRENT_DATE -1825 Jay

JoshuaBixby · Answer

For one, make sure the date field is indexed or you will be suffering some terrible performance with data from the layer. When working with ranges in file geodatabases, whether dates or numbers in general, BETWEEN performs better. What happens if you try: Status = 'Completed' AND (Delivery_Date BETWEEN date '2017-01-15 00:00:00' AND date '2018-01-15 00:00:00')‍