Get data between date range in SQLite

Question

I am trying to get data between date range. Date is saved as TEXT in my database (dd-MM-YYYY format) When I fire the below query results are null.

SELECT * FROM appointment_table WHERE date BETWEEN cast( '08-02-2017' as datetime ) AND cast( '16-02-2017' as datetime ) AND barber_id = 36

The correct way for getting data between date ranges when date is saved in TEXT format irrespective of the date format is as below

SELECT * FROM appointment_table WHERE date BETWEEN '08-02-2017' AND '16-02-2017' AND barber_id = 36

You're casting your range to datetime, but the column date is still a TEXT. Probably doesn't make sense — Xavier Rubio Jansana, Feb 16 '17 at 13:00
Look at the response in this topic : http://stackoverflow.com/questions/14207494/android-sqlite-select-between-date1-and-date2 — yozzy, Feb 16 '17 at 13:03
@MaheshwariReddy Have you run in SQL to check that is it returning any data ? — Piyush, Feb 16 '17 at 13:06

MatPag · Answer 1 · 2019-06-10T07:25:38.473

3

You need to use one of the format supported by SQLite described here to use BETWEEN: http://www.sqlite.org/lang_datefunc.html

So your query should become: (transform DD-MM-YYYY strings to YYYY-MM-DD date)

SELECT * FROM appointment_table WHERE barber_id = 36 AND (date BETWEEN '2017-02-08' AND '2017-02-16')

edited Jun 10 '19 at 07:25

answered Feb 16 '17 at 13:15

MatPag

1 Answers1