How to select from datetime in sqlite?

Question

Joined is a datetime data type column in the database and dates are saved as '12/05/2010 15:54:32' This my query:

SELECT * 
FROM users 
WHERE joined BETWEEN '12/05/2010 00:00:00' AND '12/05/2010 23:59:59' 
ORDER BY id ASC

But it doesn't work. It returns no rows.

So how i gan get them?

---

Solution:

SELECT * 
FROM users 
WHERE joined BETWEEN '2010-05-05 00:00:00' AND '2010-05-12 23:59:59'
ORDER BY id ASC

Answer 1

If you're storing dates as strings, you should use 'YYYY-MM-DD HH:MM:SS' format. These will sort in chronological order, and have the added benefit of being accepted in SQLite's date and time functions.

Answer 2

I realize that this is not an answer,
but perhaps it can help in narrowing down where the problem might lie.

Edit:
This also works at the shell:

sqlite> create table t (ts);
sqlite> insert into t values ('12/05/2010 15:54:32');
sqlite> SELECT * 
   ...> FROM t 
   ...> WHERE ts 
   ...> BETWEEN '12/05/2010 00:00:00' AND '12/05/2010 23:59:59';
12/05/2010 15:54:32

---

The following works for me in Python:

>>> import sqlite3
>>> conn = sqlite3.connect(":memory:")
>>> c = conn.cursor()
>>> c.execute("CREATE TABLE t (ts)")
<sqlite3.Cursor object at 0x7fe88ebbac90>
>>> conn.commit()
>>> c.execute("INSERT INTO t VALUES ('12/05/2010 15:54:32');")
<sqlite3.Cursor object at 0x7fe88ebbac90>
>>> conn.commit()
>>> c.execute("""
SELECT * 
FROM t 
WHERE ts 
BETWEEN '12/05/2010 00:00:00' AND '12/05/2010 23:59:59'
""").fetchall()
[(u'12/05/2010 15:54:32',)]