vendredi 23 janvier 2015

SQLite date compare redux

I've read a great many SO posts on this but nothing is working for me. My problem is that this query returns every record, regardless of the actual value in createdDateTime



SELECT ID, createdDateTime
FROM Events
WHERE createdDateTime <= date('2015-01-01')


Results include:


enter image description here


I tried using WHERE createdDateTime <= datetime('2015-01-01 00:00:01') but it didn't change anything.


Here is the structure of my DB... (I'm using the SQLite Manager extension in Firefox) enter image description here


Here is some of the data...


enter image description here


I also tried doing this through the python interpreter, in case it was SQLite Manager that was the problem.



>>> import sqlite3 as sql
>>> con = sql.connect('RA_summary.sqlite')
>>> cur = con.cursor()
>>> cur.execute("SELECT ID, createdDateTime FROM Events WHERE createdDateTime <= date('2015-01-01')")
<sqlite3.Cursor object at 0x1007718f0>
>>> all_rows = cur.fetchall()
>>> for row in all_rows:
... print ('{0} : {1}'.format(row[0],row[1]))


Results include...


363 : 10/15/2014 12:44:30 AM


364 : 10/15/2014 12:44:30 AM


365 : 10/15/2014 12:45:30 AM


366 : 10/15/2014 12:45:30 AM


367 : 10/15/2014 12:46:30 AM


I tried using the strftime() function instead of date (although they can be the same per the documentation at http://ift.tt/11EE2rC) Still got wrong results.


Thanks if you made it this far!


Eric B.


Aucun commentaire:

Enregistrer un commentaire