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
– Xaver Kapeller, Commented Jul 30, 2014 at 12:24
stackoverflow.com/questions/1469689/… Sometimes answers can come about through a google search. — bhuvy
– bhuvy, Commented Jul 30, 2014 at 12:26
@bhuvan-venkatesh and sometimes SQLite behaves different than MySQL. — Gerald Schneider
– Gerald Schneider, Commented Jul 30, 2014 at 13:27

ngrashia · Accepted Answer · 2014-07-30 12:42:46Z

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, 2014 at 12:42

answered Jul 30, 2014 at 12:31

ngrashia

9,8945 gold badges45 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

lacas Over a year ago

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)

ngrashia Over a year ago

You can use strftime('%Y-%m-%d %H:%M', my_date )

ngrashia Over a year ago

@lacas: Refer edit. Please upvote and accept as answer if it helped you..

lacas Over a year ago

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+"')";

Android is everything for me · Accepted Answer · 2014-07-30 12:32:41Z

0

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

answered Jul 30, 2014 at 12:32

Android is everything for me

3,7914 gold badges30 silver badges38 bronze badges

Collectives™ on Stack Overflow

SQLite query between dates

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related