Android SQLite select between two dates

Question

I store date as TEXT in my database in this format YYYY-MM-DD. Each row has start date and final date. I want to select the row that today date is between the start date and final date. Today date is string and is in the same format as those ex.2014-07-29 I've tried

SELECT * FROM TABLE_NAME WHERE date(todayDate) BETWEEN date(COLUMN_START_DATE) AND date(COLUMN_FINAL_DATE)

but it didn't work. I get 0 row. I've also tried

SELECT * FROM TABLE_NAME WHERE strftime('%Y-%m-%d', todayDate) BETWEEN COLUMN_START_DATE AND COLUMN_FINAL_DATE

Still not work. What am I doing wrong?

Did you check [Compare Dates in SQLite](http://stackoverflow.com/questions/13091000/sqlite-compare-dates) — Apoorv, Jul 29 '14 at 10:22
@Apoorv I did and I followed, but I failed to notice that there must be single quote before and after the string in the application. I just noticed this after I checked it again, my bad. Thank you so much. — user3034432, Jul 29 '14 at 10:40

score 0 · Answer 1 · answered Jul 29 '14 at 10:19

use this to get the results:-

String selection = "from_duration<=Datetime('" + initial_date
            + "') AND to_duration>=Datetime('" + final_date + "')";
your_database.query(Table_Name,Columns_youwant, selection,
            null, null, null, null);

Android SQLite select between two dates

1 Answers1