How to search in sqlite by date?

Question

I'm saving im my table a colum current_date in this format: 'yyyy-MM-dd HH:mm'

How to search in database by date? I want, for examble the records from 2013-09-25 discarting the hour.

If I do this "Select current_date from mydatabase where current_date = '2013-09-25'" It does not work. I have to make this "Select current_date from mydatabase where current_date = '2013-09-25 09:25'". But i want only the record based on the date, not based on the date and hour.

Have you tried saying `WHERE SUBSTR(current_date, 1, 10) = '2013-09-25'`? — PP., Sep 26 '13 at 14:38

Kanwaljit Singh · Answer 1 · 2013-09-26T14:58:13.360

2

SELECT * FROM mydatabase WHERE ( current_date > '2011-08-07') AND (current_date < '2011-08-08')

Replace the first date with the day you want information about and then update the second date with the following day.

edited Sep 26 '13 at 14:58

answered Sep 26 '13 at 14:44

Kanwaljit Singh

4,339
2
18
21

1

backticks are so MySQL – njzk2 Sep 26 '13 at 14:49
With a normal (case-sensitive) index, this can be better optimized than LIKE. – CL. Oct 21 '13 at 06:45

score 2 · Accepted Answer · answered Sep 26 '13 at 14:50

2

Apparently your dates are stored as Strings. You can use LIKE to discard the hour :

 select current_date from mydatabase where current_date like '2013-09-25%'

answered Sep 26 '13 at 14:50

njzk2

38,969
7
69
107

how can I do this on rawQuery? 'rawQuery(sql, selectionArgs' – Roland Sep 26 '13 at 15:06

score 0 · Answer 3 · answered Oct 21 '13 at 06:18

You can directly use date function of sqlite

Select current_date from mydatabase where date(current_date) = '2013-09-25'

instead of

Select current_date from mydatabase where current_date = '2013-09-25'

Extra:- If you want to fetch using time then you can also do that using time function

Select current_date from mydatabase where time(current_date) = '09:25'

How to search in sqlite by date?

3 Answers3