python - How to use function - CAST(datetime as date) of SQL Server in sqlalchemy? -

i have table has columns - id(int), date(datetime), value(float). wanted output daily average group date of rows has id=2.

i wrote following sql query , query working fine:

select cast(date date), avg(value) table id=2 group cast(date date) 

for same problem, if wanted execute in sqlalchemy, using following expression :

>> sqlalchemy import sql, types >> x = sql.cast(table.date, types.date) 

but when tried executing x, got following error :

>> x.execute()     argumenterror: not executable clause: cast(table.date datetime) 

which means sql server considering types.date datetime. please ignore argumenterror. trying focus on internal sql query using cast(table.date datetime) rather cast(table.date date) there way can convert datetime date in sqlalchemy?

following wrong code:

1. you cannot execute clause. need have statement or query (see below). reason error originally.
2. indeed, if cast sa.date, mssql engine cast datetime. not entirely sure why is, suspect because older versions of mssql did not have separate date-only datatype. solve this, simple cast date datatype found in mssql dialect.

the code below should work. not have mssql right now, show compilation of sql statement mssql instead:

from sqlalchemy import create_engine, column, integer, float, date, datetime, func sqlalchemy.dialects.mssql import date sqlalchemy.orm import sessionmaker sqlalchemy.ext.declarative import declarative_base  # @todo: replace connection string engine = create_engine('sqlite:///:memory:', echo=true) session = sessionmaker(bind=engine) session = session()  base = declarative_base()   class mytable(base):     __tablename__ = 'my_table'     id = column(integer, primary_key=true)     date = column(datetime())     value = column(float())  base.metadata.create_all(engine)  def get_mssql_sql(qry):     sqlalchemy.dialects import mssql     dialect = mssql.dialect()     return str(qry.compile(dialect=dialect))   # date = date  # @note: not work in mssql (shows problem-2) q = (session      .query(func.cast(mytable.date, date), func.avg(mytable.value))      .filter(mytable.id == 2)      .group_by(func.cast(mytable.date, date))      ) print(get_mssql_sql(q.statement))  # in code, run below execute: rows = q.all()