There are some problems when using Sqlalchemy to query the data between time1 and time2-CodePudding

My database is SQL Server 2008.

The type of time character I want to query in the database (such as finishdate) is datetime2

I just want data between "10-11" and "10-17". When using Sqlalchemy, I use

cast(FinishDate, DATE).between(cast(time1, DATE),cast(time2, DATE))

to query dates, but it does not return any data (I confirm that there must be some data statements meet the query time range)

==============================================

from sqlalchemy import DATE


bb = "2021-10-11 12:21:23"  
cc = "2021-10-17 16:12:34"  
record = session.query(sa.Name cast(sa.FinishDate, DATE)).filter(
                cast(sa.SamplingTime, DATE).between(cast(bb, DATE), cast(cc, DATE)),
                sa.SamplingType != 0
            ).all()

record = session.query(sa.Name cast(sa.FinishDate, DATE)).filter(
                cast(sa.SamplingTime, DATE)>= cast(bb, DATE),
                sa.SamplingType != 0
            ).all()

Both return []

Something is wrong with my code and I don't know what the trouble is.

CodePudding user response：

It is working for me, I only changed the DATE that you are using to Date

from sqlalchemy import Date
record = session.query(
    sa.Name cast(sa.FinishDate, Date)
 ).filter(
    cast(sa.SamplingTime, Date).between(
        cast(bb, Date), cast(cc, Date)
    ),
    sa.SamplingType != 0
 ).all()

As a matter of fact first parameter of cast can be a string also, so in this case its fine to pass date as string in cast.

:param expression: A SQL expression, such as a
         :class:`_expression.ColumnElement`
         expression or a Python string which will be coerced into a bound
         literal value.

CodePudding user response：

I changed DATE to Date, but it didn't work either,and I found another problem: when using sqlalchemy ,I can't query back the latest datetime。 The latest datetime in the query result is "10-16",why not "10-18"?