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.

Answers


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

Dates as a date type in SQLite don't exist. There are a number of approaches to dealing with dates - store them as integer seconds since 1 Jan 1970 (unixepoch) or store them as strings, but if you do, then you really need to store them in 'YYYY-MM-DD' format because that is what the date functions require as input.

Assuming you use the string format in the format I suggested then your query would look something like

SELECT * FROM Table WHERE Date(Inputdate) BETWEEEN Date(startDate) AND Date(EndDate);

(although you may want to format the output of the date columns to US date format with

SELECT Strftime("%m/%d/%Y",startDate) As StartDate ...

If you use seconds since 1970 its somewhat easier because the seconds just compare without needing the convert them to dates, although you still might want to output in US date format, so ...

SELECT Strftime("%m/%d/%Y",startDate) As StartDate ... FROM Table WHERE inputDate BETWEEN startDate and EndDate;

sqlite> select *from tbl_node where mydate between '2014-02-02' and '2014-02-06';

it show the output :-

1|1|123|456|12eb-ab|1|1|254|123|19|2014-02-03 16:00:44
2|1|123|456|12eb-ab|1|1|254|123|19|2014-02-03 16:01:03
3|1|123|456|12eb-ab|1|1|254|123|19|2014-02-03 16:00:57
4|1|123|456|12eb-ab|1|1|254|123|19|2014-02-03 16:00:34

Here mydate is column name in tbl_node; we can also use from current time , using now.

sqlite> select *from tbl_node where mydate between '2014-02-02' and 'now';


Need Your Help

drill down without using code

storyboard tableview drilldown

I am quite new to xcode and app production. I have just finished watching a youtube video which uses storyboard to drill down without using any code. It involves using several Tableview controllers...