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

Need Your Help

Read EXIF-data from a byte

android bytearray byte jpeg exif

I am currently trying to get the EXIF-data from a photo in the form of a byte-array. The photo is taken directly from the camera and is therefore only available as the afore-mentioned byte[].

Is Cascade Delete automatically performed in Entity Framework 1-1 (required) Relationships?

c# entity-framework relationship cascade

Searching about the cascading behavior of Entity Framework, if noticed this answer. On short, the OP is searching for a way to perform cascade delete on 1-0 / 1-1 relationships in Entity Framework,...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.