SQLAlchemy equivalent for MSSQL query DATEADD(day, -1, GETDATE())

Question

I have very simple Microsoft SQL query which I am trying to translate into SQLAlchemy.

I read the documentation here: https://docs.sqlalchemy.org/en/13/core/tutorial.html#functions. Then, I tried several things including Python datetime, but it seems I am still missing something as SQLAlchemy beginner.

MSSQL Code:

SELECT flight_ID FROM
flight_table
WHERE FlightStartTime < DATEADD(day, -1, GETDATE())

SQLAlchemy Code:

from sqlalchemy import func


table_ref = flight_table # Used reflection

num_day = -1
stmt = select([table_ref.c.flight_ID])
where_column = flight_table.columns.FlightStartTime


stmt = stmt.where(
    where_column < func.dateadd(func.day(), num_day,
                                    func.getdate())
    )

with self.engine.connect() as conn:
    output = conn.execute(stmt).fetchall()

The error I get is following:

sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', '[42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]The day function requires 1 argument(s). (174) (SQLExecDirectW);

The error is understandable as I need to find SQLAlchemy equivalent for "day" as defined in Microsoft SQL query.

Any pointers will be appreciated.

Thanks.

[this thread](https://stackoverflow.com/questions/15572292/using-dateadd-in-sqlalchemy) deals with a similar question, it may help you — cpalmer, Jun 24 '20 at 21:37
Thanks for mentioning. Yes, I looked that before posting. It did not address the topic about SQLAlchemy equivalent for "day" as defined in Microsoft SQL query. — Alsina, Jun 24 '20 at 22:38

Gord Thompson · Accepted Answer · 2020-06-25T00:00:25.370

I need to find SQLAlchemy equivalent for "day" as defined in Microsoft SQL query

You can use the SQLAlchemy text function to create a literal that will be used in a query expression:

import sqlalchemy as sa

# ... set up your engine by calling `sa.create_engine` ...

flight_table = sa.Table("flight_table", sa.MetaData(), autoload_with=engine)

num_days = -1
stmt = sa.select([flight_table.c.flight_ID]).where(
    flight_table.c.FlightStartTime
    < sa.func.dateadd(sa.text("day"), num_days, sa.func.getdate())
)
with engine.connect() as conn:
    result = conn.execute(stmt).fetchall()
    print(result)

The SQL command text that gets generated is

SELECT flight_table.[flight_ID] 
FROM flight_table 
WHERE flight_table.[FlightStartTime] < dateadd(day, ?, getdate())

SQLAlchemy equivalent for MSSQL query DATEADD(day, -1, GETDATE())

1 Answers1