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
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',)]
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.
