Invalid datetime format in python-mysql query
<p>I wanted to fetch a record from database which is having the <code>datetime</code> field a part of primary key. I am querying from <code>python</code> using <code>mysqldb package</code> which is resulting in error </p>

<blockquote>
  <p>Invalid datetime format</p>
</blockquote>

<p>wherein the same query is working fine when executed through command line and mysql workbench.</p>

<p>My code looks like this.</p>

<pre><code>def get_all_list(table_name, modify_dt, params):
    cursr = connections['default'].cursor()
    stmt = 'select * from ' + table_name + ' where ' + params 
    cursr.execute(stmt)
    data = cursr.fetchall()
</code></pre>

<p>the params is the string which contains my primary key value.</p>

<p>for instance </p>

<pre><code>params is "join_date = '2016-09-08 00:00:00+00:00'"
</code></pre>

<p>my final query looks similiar to this</p>

<pre><code>select * from employee where join_date = '2016-09-08 00:00:00+00:00'
</code></pre>

<p>When executed, the same is providing result in mysql workbench while when executed through my program , I am getting error as </p>

<blockquote>
  <p>Incorrect datetime value: '2016-09-08 00:00:00+00:00' for column
  'join_date' at row 1</p>
</blockquote>

<p>Date format should be</p>

<pre><code>'2016-09-08 00:00:00'
</code></pre>

<p>if you need to set time zone, you need a separate SQL query</p>

<pre><code>set time_zone='+00:00';
</code></pre>

<p>See also:  <a href="http://stackoverflow.com/questions/930900/how-to-set-time-zone-of-mysql">How to set time zone of mysql?</a></p>

<hr>

<p>If you are using django, you can set time_zone for the entire app in the settings file:</p>

<pre><code>DATABASES['default'] = {
    'ENGINE': 'your_db_engine',
    'NAME': 'my_database',
    'USER': 'username',
    'PASSWORD': 'password',
    'HOST': 'localhost',
    'PORT': 3306,
    'OPTIONS': {
        "init_command": "SET storage_engine=InnoDB; set time_zone='+00:00';",
    },
}
</code></pre>

python mysql python-2.7 datetime