SQLAlchemy : Convert to date type

Question

Here is my mssql code snippet

Select
     Sum((Case
            When (me.status in ('CLOSED','VOID') and
            Convert(DATE,me.end_dt)=CONVERT(DATE,GETDATE()))
            Then 1 else 0
            end)) as completed,

     Sum((Case
            When datediff 
            ( hh, cast (CONVERT (DATE, GETDATE()) as varchar)+' '+ '00:00:00.000', me.due_dt)
            between 12 and 24 and me.status not in ('CLOSED','VOID')
            Then 1 else 0
            end)) as Count_12_to_24 

     from pvr_svc me with(nolock)

For the first chunk of query this is what i have tried so far.

expr = func.sum(case([(and_(pvr_svc.status.in_(['CLOSED','VOID']), 
       (func.convert(DATE, pvr_svc.end_dt)== func.convert(DATE, func.current_date()))
       ),1)],else_=0)).label('com')

session.query(expr).scalar()

But i get an error saying.

****** NotImplementedError: Don't know how to literal-quote value (class 'sqlalchemy.types.DATE')***

I have few questions here,

Q1. How do i convert the datetime column values (in the above case me.end_dt) to DATE type?

Q2. What is the equivalent of the GETDATE() in sqlalchemy? (i have tried func.current_date(), func.now())

Q3. What is the right way of implementing the last chunk of the mssql code snippet?

I have went through the sqlalchemy doc, found some information on the stackoverflow, below are the links,

convert selected datetime to date in sqlalchemy

Comparing Dates with SQLAlchemy, SQLite

score 0 · Answer 1 · answered Oct 10 '14 at 16:35

0

expr = func.sum(
    case([(and_(
        pvr_svc.status.in_(['CLOSED', 'VOID']),
        func.strftime('%Y-%m-%d', pvr_svc.end_dt) == func.date('now'),
    ), 1)], else_=0)
).label("com")

This will do string comparison of DATE only (which is what you want) but using string representations in YYYY-MM-DD format, so all the comparisons will work as expected.

answered Oct 10 '14 at 16:35

van

74,297
13
168
171

Thanks for the answer van. I get an error `*** ProgrammingError: (ProgrammingError) ('42000', 319, "[unixODBC][FreeTDS][SQL Server]Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.", 8466) "SELECT sum(CASE WHEN (pvr_svc.status IN ('CLOSED', 'VOID') AND strftime('%Y-%m-%d', pvr_svc.end_dt) = date('now')) THEN 1 ELSE 0 END) AS com \nFROM pvr_svc AS pvr_svc WITH (NOLOCK)" ()`. The pvr_svc.end_dt column has few entries as Null. – Ameet S M Oct 11 '14 at 07:04
as you can see, the error has nothing to do with *date comparison*, but rather with the `WITH (NOLOCK)` statement. So you should look for the cause of the problem there. Or at least share your code which adds it if you would like a feedback on that part as well. The answer I wrote is for `sqlite` database. and you will need other `func.xxx` functions for a different database engine. – van Oct 11 '14 at 07:18
If i remove the func.xxx then i will not get any error. Since i am connecting to mssql db should i use sqlalchemy.dialects.mssql.DATETIME2 ? I unable find the correct syntax for this. – Ameet S M Oct 11 '14 at 08:09

Ameet S M · Accepted Answer · 2014-10-14T10:07:18.297

This is the solution which i have got,

from sqlalchemy import DATE, cast

expr1 = func.sum(case([(
        and_(
            (func.convert(literal_column('DATE'), pvr_svc.due_dt) == func.convert(literal_column('DATE'), func.getdate())), 
            pvr_svc.status.in_(['CLOSED','VOID'])
            ),1
        )],else_=0)).label('completed')

expr2 = func.sum(case([(
    and_(
        func.datediff(literal_column('hh'), cast(func.convert(literal_column('DATE'), func.getdate()), VARCHAR)
            +" "+"00:00:00.000",pvr_svc.due_dt)
            .between(0,12),~pvr_svc.status.in_(['CLOSED','VOID'])
        ),1)],else_=0)).label('Count_0_to_12')

session.query(expr1, expr2).all()

or,

we can use cast function instead of convert function.

expr1 = func.sum(
           case([(and_
             ((cast(pvr_svc.end_dt,DATE) == cast(func.getdate(),DATE)),
               pvr_svc.status.in_(['CLOSED','VOID'])
             ),1)],else_=0)
           ).label('com')

session.query(expr1).all()

There is no difference between cast and convert w.r.t to performance, as cast function is internally is implemented as convert function in sql. Find more info here.

SQLAlchemy : Convert to date type

2 Answers2