How To Compare Dates In Sqlalchemy?

I have the following simple setup, where fromDate and toDate are strings on the format 'YYYY-MM-DD': class SomeType(Base): date = Column(DateTime) def findAll(fromDate, toDate

Solution 1:

How about using datetime.datetime objects instead of strings for fromDate, toDate?

from datetime import datetime, timedelta

def findAll(fromDate, toDate):
    fromDate = datetime.strptime(fromDate, '%Y-%m-%d')
    toDate = datetime.strptime(toDate, '%Y-%m-%d') + timedelta(days=1)
    return session.query(SomeType).filter(
        SomeType.date >= fromDate,
        SomeType.date < toDate).all()

Solution 2:

The problem is that your SomeType.date column is not simple date, but is datetime column, so it contains also a time component.

This type mismatch is the cause of your problem. If this is the case then following should work:

session.query(SomeType).filter(func.date(SomeType.date) >= fromDate, func.date(SomeType.date) <= toDate).all()

where we basically castdatetime to date using DATE(...) function of MySql.

However, I would probably also prefer working with date(time) data types instead of strings. You are just lucky that most databases implicitly allow parsing of ISO-compliant string representations of DATEs.