Using Microsoft Access SQL operators in Python ODBC

Question

Short version: When I try to use Access's DatePart function via ODBC, it cannot be resolved.

Longer version:

I have a Microsoft Access query which returns rows with a timestamp and a score. I want to sort it by day and then by score - effectively a high-score table for the day.

For want of a better function, I used the DatePart function to extract each of the Year, Month and Day from the timestamp, and ORDER BY them followed by Score.

In Microsoft Access, the query works beautifully.

However, when I use pyodbc to access the same query, the ODBC driver is stumped by the DatePart function, and thinks it is the name of a missing parameter.

What astonished me was that even if I hid the DatePart function, by creating a new HighScore query, and then SELECT * FROM HighScore, it still complained that it couldn't find the parameter. Apparently, the query's SQL is being resolved fairly late in the process.

My question is either:

• How do I resolve the DatePart function in the SQL to allow Access to run it, or
• What is a right way to sort by the day part of a timestamp over ODBC?

Added additional info:

Seems like overkill, but here's some code:

import pyodbc
access_db_path = r"<ellided>"
connection_string = 'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ='+access_db_path
connection = pyodbc.connect(connection_string, autocommit = True)

print "First query"
connection.cursor().execute('SELECT ScoreTime FROM SplitExtendedP1')
print "Worked"

print "Second query"
print connection.cursor().execute('SELECT DatePart("yyyy",ScoreTime) FROM SplitExtendedP1')
print "Doesn't get here."

Here are the results:

First query
Worked
Second query
Traceback (most recent call last):
  File "<ellided>.py", line 16, in <module>
    print connection.cursor().execute('SELECT DatePart("yyyy", ScoreTime) FROM SplitExtendedP1')
pyodbc.Error: ('07002', '[07002] [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. (-3010) (SQLExecDirectW)')

Answer 1

Are you sure that using quotes "yyyy" instead of apostrophes 'yyyy' is valid in that dialect of SQL?

Answer 2

Datetimes are stored in access as floating point numbers. The number to the left of the decimal point is the date, the fractional part to the right of the decimal point is the time (expressed in terms of a fraction of a day; ie, .5 = Noon).

If you want to sort by the day, you can simply use the Int() function to return the integer portion of the datetime field.