Trouble using date from pyodbc query
Question: I am working with an Access 2003 database using python 2.7 connecting with
pyodbc. Windows 7

I am having trouble making sense of a date query.

In the trivial example below, the first query collects the user's date of
birth 'dob' which in the database is a datetime.datetime object.

print row >>>> ('A0103', 'Susan', datetime.datetime(1986, 4, 10, 0, 0))

When we collect the date & print it

dob = res[2]

we get

print dob >>>> 1986-04-10 00:00:00

print type(dob) >>> type 'datetime.datetime'

But when used in the second query.

"SELECT name FROM users WHERE dob = %s" % dob"

we get:

ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Microsoft Access Driver]
Syntax error (missing operator) in query expression 'dob = 1986-04-10
00:00:00'. (-3100) (SQLExecDirectW)")

extracting just the date & using datetime.date to recreate a datetime object:

y, m, d = dob.year, dob.month, dob.day

mydate = date(y, m, d)

allows for a sucsesfull query transaction,

the query "SELECT id, name FROM Siswa WHERE name ='%s' AND dob => %s" % (nama,
mydate)

yields ('A0103', 'Susan', datetime.datetime(1986, 4, 10, 0, 0)

but would also deliver any other Susan that were younger

I would expect the query

"SELECT id, name FROM Siswa WHERE name ='%s' AND dob = %s" % (nama, mydate)

to yield ('A0103', 'Susan', datetime.datetime(1986, 4, 10, 0, 0)

but it comes up empty.

What am I missing ?

    
    
    from datetime import datetime, date
    
    sql = "SELECT name, dob FROM users"
    result1 = cur.execute(sql).fetchall()
    
    for row in result1:
        print row
        name, dob = row[1], row[2]
        print dob
    
        try:
            sql = "SELECT name FROM users WHERE dob = %s" % dob  
            result2 = cur.execute(sql).fetchall()
            print result
        except:
            y, m, d = dob.year, dob.month, dob.day
            mydate  = date(y, m, d)
    
            sql = "SELECT name FROM users WHERE dob => %s" % dob  
            result2 = cur.execute(sql).fetchall()
            print result
    
            sql = "SELECT name FROM users WHERE dob = %s" % dob  
            result2 = cur.execute(sql).fetchall()
            print result
    


Answer: I run with the same problem, of not being able to query dates.

My solution turned out to be:

    
    
    SELECT name FROM users WHERE DateValue(registered) <= '20-04-2015'
    

So `DateValue()` seems to be the solution for comparing dates.

