CODEWITHSUNDEEP
iossqldatabasesqlite
barrelmp
barrelmp

Reputation: 31

How do you select all the rows between two values in SQLite?

I'm building an iOS app where I want to retrieve all the values from my database between two dates that the user picks. So for example, I want all the rows from the 1st of March to the 5th of March. Would look something like 

SELECT * FROM MAIN WHERE DATE = '01/03/2020' AND ENDS ='05/03/2020'

So from that I would hope to retrieve all data from the 1st,2nd,3rd,4th and 5th of march. Any ideas on how to do this? Thank you

Upvotes: 2

Views: 1241

Answers (3)

Rob
Rob

Reputation: 437532

There are two issues:

  1. Date types:

    As Datatypes In SQLite Version 3 says:

    2.2. Date and Time Datatype

    SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

    • TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").

    • REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.

    • INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.
       

    Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

    So storing dates in a dd/MM/yyyy format (using the DateFormatter capitalization convention) is problematic because in the absence of a native date type, it’s going to store them as strings, and therefore all comparisons will be done alphabetically, not chronologically, sorting values like 03/10/2009 (or nonsense strings like 02foobar, for that matter) in between the strings 01/05/2020 and 05/05/2020.

    If, however you store them as yyyy-MM-dd, then it just so happens that alphabetical comparisons will yield chronologically correct comparisons, too.

  2. SQL syntax:

    Once you have your dates in your database in a format that is comparable, then if you have all of your dates in a single column, you can use the BETWEEN syntax. For example, let’s say you stored all of your dates in yyyy-MM-dd format, then you could do things like:

    SELECT * FROM main WHERE date BETWEEN '2020-03-01' AND '2020-03-05';

    But needless to say, you can’t use this pattern (or any comparison operators other than equality) as long as your dates are stored in dd/MM/yyyy format.

Upvotes: 1

VBoka
VBoka

Reputation: 9083

If you want to show all the data that has values of column "date" between this two dates then:

Select * 
from MAIN
where `date` between '01.03.2020' and '05.03.2020';

If you want to show all the data that has values of column "ends" between this two dates then:

Select * 
from MAIN
where ends between '01.03.2020' and '05.03.2020';

If you want to show all the data that has values of columns "date" and "ends" between this two dates then:

Select * 
from MAIN
where ends between '01.03.2020' and '05.03.2020'
and `date` between '01.03.2020' and '05.03.2020';

Here is a demo

Upvotes: 0

Justin Mensah
Justin Mensah

Reputation: 23

Try to use comparison operators like: 

DATE >= '01/03/2020' AND DATE <= '05/03/2020'

Upvotes: 1

Related Questions