SQLite query between dates

Question

select * from table_my where my_date between '2014-07-23 00:00:00' and '2014-07-30 00:00:00' order by _id desc

Here is my query, I d like to create this simple date compare query, but not working. This query don't add anything out. Why? How can I compare dates in sqlite?

SQLite does not have a `DateTime` datatype! To compare dates get the epoch time from the `Date` with `getTime()` (it's a long) and save that instead in the database. Comparing two numbers is obviously very easy. — Xaver Kapeller, Jul 30 '14 at 12:24
http://stackoverflow.com/questions/1469689/select-mysql-query-between-date Sometimes answers can come about through a google search. — bhuvy, Jul 30 '14 at 12:26
@bhuvan-venkatesh and sometimes SQLite behaves different than MySQL. — Gerald Schneider, Jul 30 '14 at 13:27

ngrashia · Accepted Answer · 2014-07-30T12:42:46.177

2

Try below query using strftime date functions.

SELECT * from table_my
WHERE strftime('%Y-%m-%d', my_date )
     between  
          strftime('%Y-%m-%d', '2014-07-23') 
          and 
          strftime ('%Y-%m-%d', '2014-07-30');

EDIT:

To use with hours and minutes, you can try something like:

SELECT * from table_my
WHERE strftime('%Y-%m-%d  %H:%M', my_date )
     between  
          strftime('%Y-%m-%d  %H:%M', '2014-07-23 11:22') 
          and 
          strftime ('%Y-%m-%d  %H:%M', '2014-07-23 11:25');

edited Jul 30 '14 at 12:42

answered Jul 30 '14 at 12:31

ngrashia

9,869
5
43
58

It looks like this working, but Can u help me how to use this to use minutes in this query? So: yyyy-mm-dd HH:MM (2014-07-23 11:22 and between 2014-07-23 11:25) – lacas Jul 30 '14 at 12:39
You can use `strftime('%Y-%m-%d %H:%M', my_date )` – ngrashia Jul 30 '14 at 12:41
@lacas: Refer edit. Please **upvote** and **accept as answer** if it helped you.. – ngrashia Jul 30 '14 at 12:43
1

Thanks now it is working, my code look like this String sql = "SELECT * from "+SQLLiteHelper.TABLE_MY +" WHERE strftime('%Y-%m-%d %H:%M:%S', mdate ) between strftime('%Y-%m-%d %H:%M:%S', '"+nnow+"') and strftime ('%Y-%m-%d %H:%M:%S', '"+nnowminus+"')"; – lacas Jul 30 '14 at 12:45

score 0 · Answer 2 · answered Jul 30 '14 at 12:32

select * from table_my where my_date between '2014-07-30' and '2014-07-23' order by _id desc

use above query may it will work to you i also got such kind of problem during coding i think it is coming cause your using lower value before greater value

may it will help you just chek it

i just swiped the two dates also i removed timestamp

SQLite query between dates

2 Answers2