Order by Year element of DateTime in sqlalchemy query

Question

I have a Sybase table, declaritively mapped using sqlalchemy as follows:

from   sqlalchemy.ext.declarative import declarative_base    
Base = declarative_base()

class Appointment(base):
    __tablename__ = 'APPOINTMENT'
    __table_args__ = {'quote':False}

    dt = Column(Date, name='dt_appointment')

How do I query this table, ordering by Year(dt_appointment)? Year() is a valid sybase T-SQL function. I've tried including a calculated column e.g.:

dtYear = Column(Integer, name='Year(dt_appointment)', quote=False)

This doesn't work with the query:

session.Query(Appointment).order_by(Appointment.dtYear)

Answer 1

You should be able to use Function expression:

from sqlalchemy.sql import func
qry = session.query(Appointment).order_by(func.DATE(Appointment.dt))

---

Note however, that if you use these types of filters often, it is advisable to create a computed column in your table which will only contain a year value of dt_appointment and create an index on this column. This will improve the speed of your queries/filters substantially. [I have no knowledge of Sybase, so I assume that in Sybase one can create a computed persistent column, as one can do in MSSQL].