title: Python: pyodbc, Microsoft Access: update dates in table, using # date qualifier
body: <p>I am trying to update some records in a Microsoft Access database, using pyodbc in Python. I am struggling in updating date/time fields in Access, which would require a # date qualifier symbol in the sql statement.</p>
  
  <p>A sample of a statement could be</p>
  
  <p><code>sql = '''UPDATE [TABLE] SET[STARTDATE]=#2018-10-25# WHERE[KEYCOLUMN]=12345;'''</code></p>
  
  <p>Using a parameter query, that would look like</p>
  
  <pre><code>sql = '''UPDATE [TABLE] SET[STARTDATE]=? WHERE[KEYCOLUMN]=?;'''
  params = (#2018-10-25#, 12345)
  cursor.execute(sql, params)
  </code></pre>
  
  <p>However, the execution crashes because of the required # symbols for the data qualifier. 
  Also using the datetime library is not helpful, as the following parameters are also resulting in a failed execution:</p>
  
  <pre><code>params = (datetime.date(2018,10,25), 12345)
  cursor.execute(sql, params)
  </code></pre>
  
  <p>What would be the correct syntax/workaround for updating DateTime fields, including blanking a DateTime field in an Access database?</p>
  
  <p><strong>Error codes when trying different attempts (with the actual table names and field names, instead of the sample names used in above example)</strong>:</p>
  
  <p>When passing the date as a string</p>
  
  <pre><code>SQL: UPDATE [Status Scorecard] [IN:Start Execute] = ? WHERE [IN:ID]=?;
  Params: ('2015-09-14', '47977')
  Error:(&lt;class 'pyodbc.ProgrammingError'&gt;, ProgrammingError('42000', '[42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement. (-3503) (SQLExecDirectW)'), &lt;traceback object at 0x07E24120&gt;)
  </code></pre>
  
  <p>When passing the data with # date delimiter symbol:</p>
  
  <pre><code>sql = '''UPDATE [Status Scorecard] [IN:Start Execute] = ? WHERE [IN:ID]=?;'''
  params = (#2015-09-14#, '47977')
  print(params)
  r = axSCORECARD.updateSQL(sql, params)
  </code></pre>
  
  <p>The code does not run, as the # symbol is treated as a comment, resulting in a code syntax error.</p>
  
  <p>When passing the data as a string, using the # date delimiter inside the string:</p>
  
  <pre><code>SQL: UPDATE [Status Scorecard] [IN:Start Execute] = ? WHERE [IN:ID]=?;
  Params: ('#2015-09-14#', '47977')
  Error:(&lt;class 'pyodbc.ProgrammingError'&gt;, ProgrammingError('42000', '[42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement. (-3503) (SQLExecDirectW)'), &lt;traceback object at 0x07E24148&gt;)
  </code></pre>
  
  <p>When passing the date as a datetime.date call (after having added 'import datatime' to the script):</p>
  
  <pre><code>SQL: UPDATE [Status Scorecard] [IN:Start Execute] = ? WHERE [IN:ID]=?;
  Params: (datetime.date(2015, 9, 14), '47977')
  Error:(&lt;class 'pyodbc.ProgrammingError'&gt;, ProgrammingError('42000', '[42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement. (-3503) (SQLExecDirectW)'), &lt;traceback object at 0x07E240D0&gt;)
  </code></pre>