Between operation for date in SQLite database
I have a table student with the following columns:
no - integer name - string startdate - date enddate - date.
Date format is MM/DD/YYYY.
I will give a date as input. Now I need a query the inputdate which found in between the start and end date.
For an example I will give 04/14/2012, then the query should return the 1st record as in the figure.
(because input date (04/14/2012) is found in between the 04/10/2012 to 04/20/2012)
Please help me.
The issue you are having is caused by your assumption that sqlite has a date/datetime type when in fact it doesn't.
I suggest you read the following http://www.sqlite.org/datatype3.html to have a better understanding of sqlite types.
The dates in the MM/DD/YYYY format are handled as TEXT by sqlite, and so those dates are compared as strings. For example, 02/01/2012 is considered bigger than 01/02/2012by sqlite if compared directly.
You will need to transform those dates to a format that can be string-compared. Here is an example:
sqlite> create table foo (d TEXT); sqlite> insert into foo values ('02/01/2012'); sqlite> select substr(d, 7, 4) || substr(d, 1, 2) || substr(d, 4, 2) from foo; 20120201
You should post what you have tried so far.
There should be a between clause that you can use:
select * from table where inputdate between startdate and enddate