SqlLite select query for between date range with date format mm-dd-YYYY

Question

I'm using SQLite, I want to get all the students who are having their DOB between 01-01-2000 and 12-12-2010. The dates are in TEXT and mm-dd-YY format in my database. I've done like this:

 SELECT * FROM student_details
  WHERE strftime('%m-%d-%Y', studentDob)
BETWEEN strftime('%m-%d-%Y', '01-01-2000')
    AND strftime('%m-%d-%Y', '12-12-2010')

But it is not giving any result even though the records are there in my DB. Please help me if there any wrong in my query. It not even producing any error.

score 3 · Accepted Answer · edited May 23 '17 at 11:43

3

The American date format is inherently unsortable because units are not arranged in consistently increasing or consistently decreasing order. An ISO (Japanese) style date, however, can be sorted alphabetically making it the most superior of human-readable (and computer sortable) forms.

In the absence of a practical date format you have to convert that text string into something sortable, e.g. a Japanese form text string, or an integer.

For SQLite see Date and Time Functions.

For parsing date strings in SQLite see this StackOverflow answer.

edited May 23 '17 at 11:43

Community

1
1

answered Aug 22 '13 at 13:12

PP.

10,764
7
45
59

1

where substr(column,7)||substr(column,4,2)||substr(column,1,2) between '20101101' and '20101130'... this is working good. – Arun Kumar Mudraboyina Aug 22 '13 at 14:23

SqlLite select query for between date range with date format mm-dd-YYYY

1 Answers1