I have a huge database with Sightings from whales and dolphins from 1995 till today. So I want to sort the database with python.

Till now I can make FeatureLayers for each year

for year in range(1995,2014): arcpy.MakeFeatureLayer_management ("Sightings95-14", "test%s" % year, "DATE >= date'01.01.%s' AND DATE < date'01.01.%s'" % (year, year+1))

now I want to sort it into each month (no matter of the yaer), because there are way more sightings in may than in december for example.

If I excecut this code:

for month in range (01,12): arcpy.MakeFeatureLayer_management ("Sightings95-14", "test%s" % month, "DATE >= date'01.%s.1995' AND DATE < date'31.%s.2014'" % (month, month+1))

it makes the 12 FeatureLayers but in each Layer is the hole database included so he did not "sort" it right.

What is my mistake ?

1 Answer 1


Years and Months behave differently in several ways that make your expression work for years, but break for months. Any set of year ranges that are positive will work with the original years expression. No set of ranges across multiple years alone can select just dates from a given month. For months you have to set an overall range of all dates regardless of the month and then add a separate required condition to only include dates that match a given month. So, if this is a File Geodatabase this should work (I assume you want 31.12.2014 included for December, so the expression had to be corrected to do that, or else use DATE <= date'31.12.2014'):

for month in range (01,12): arcpy.MakeFeatureLayer_management ("Sightings95-14", "test%s" % month, "DATE >= date'01.01.1995' AND DATE < date'01.01.2015' AND EXTRACT(MONTH FROM DATE) = %s" % (month))

If this is SDE, see your specific SDE database SQL Reference for the equivalent expression to do what the EXTRACT function is doing.


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.