Update query to cast datetime as Julian date

[GoogleCodeExporter]

import pyodbc, datetime

db = r"E:\TEMP\WalrusPathProcessing.mdb"
DRIVER = 'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}'
cnxn = pyodbc.connect(DRIVER, DBQ=  db, autocommit=True)
cursor = cnxn.cursor()

#  VB format of SQL statment:
#  SQL = 'UPDATE FilteredLocations_tbl SET JDay = Format([dt],"y") WHERE 
(((Deployment)=371));'
#  I am struggling to compose this with a Python funciton.
SQL = 'UPDATE FilteredLocations_tbl SET JDay = ([dt]).strftime(%j))  WHERE 
(((Deployment)=371));'
cursor.execute(SQL)

cnxn.commit()
cursor.close()
cnxn.close()

#####What is the expected output?
## I expect this to update the field [Jday] with the Julian date
## of the input field [dt].
#####What do you see instead?
## I get an error submitting the cursor.execute(SQL).
## Is there a way to call a function on the [datetime field] that may
## be passed to the cursor.execute? 

####What version of the product are you using? Python 2.4 on Windows XP.

Original issue reported on code.google.com by Tony.Fis...@gmail.com on 15 Oct 2010 at 10:28

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

I have tried this with an updated version of Python (2.2.6) and pyodbc (2.1.8).
I still am unable to commit the update to the table fields.
Please see code below.

## Run with
## pyodbc 2.1.8 (c:\python26\lib\site-packages\pyodbc.pyd)
## Python 2.6.6
## windows verion (5, 1, 2600, 2, 'Service Pack 3')

import pyodbc

db = r"D:\TEMP\test.mdb"
DRIVER = 'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}'
cnxn = pyodbc.connect(DRIVER, DBQ=  db, autocommit=True)
cursor = cnxn.cursor()

SQL = "SELECT Test_tbl.Deployment, Test_tbl.relocation, Test_tbl.dt, 
Test_tbl.JDay FROM Test_tbl;"

cursor.execute(SQL)
for row in cursor:
    d = row.dt
    jd = int(d.strftime('%j'))
    row.JDay = jd
    # cnxn.commit  ## I have tried placing a cnxn.commit command here, with no results
    print row.dt, row.JDay  # This shows the updated value.
                            # However, when I open up the database,
                            # I find that the values have not been updated.

#cnxn.commit   ## I have tried placing a cnxn.commit command here, with no 
results
cursor.close()
cnxn.close()

Original comment by Tony.Fis...@gmail.com on 21 Oct 2010 at 7:13

Attachments:

• test.mdb

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

I believe the error in the first report is due to the "%j".  The % character is 
an escape character in Python, so you'll need two of them:

  UPDATE FilteredLocations_tbl SET JDay = ([dt]).strftime(%%j))  WHERE (((Deployment)=371))

In the 2nd version, you've selected a value and simply replaced your variables 
value.  That isn't going to update the database.  The row objects merely hold 
the values selected, they are no longer connected to the database, so to speak.

Original comment by mkleehammer on 21 Nov 2010 at 5:17

• Changed state: NoFix