Sqlite Database - Select the Data Between Two Dates

SQLite database - select the data between two dates?

You could also just not use between.

select * from mytable where `date` >= '2014-10-09' and `date` <= '2014-10-10'

Example:

mysql> create table dd (id integer primary key auto_increment, date text);
Query OK, 0 rows affected (0.11 sec)

mysql> insert into dd(date) values ('2014-10-08'), ('2014-10-09'), ('2014-10-10'), ('2014-10-11');
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from dd where date >= "2014-10-09" and date <= "2014-10-10";
+----+------------+
| id | date       |
+----+------------+
|  2 | 2014-10-09 |
|  3 | 2014-10-10 |
+----+------------+
2 rows in set (0.01 sec)

Since it includes time, and you dont want the time. this:

select substring(date, 1, 10) from dd where substring(date, 1, 10) between '2014-10-09' and '2014-10-10';

question updated again, additional answer

Ugh. you have timestamp fields? in that case this:

select date(from_unixtime(timestamp)) from mytabel where date(from_unixtime(timestamp)) between '2014-10-09' and '2014-10-10'

finally we have arrived at sqlite

select date(datetime(timestamp, 'unixepoch')) 
  from mytable 
    where date(datetime(timestamp, 'unixepoch')) 
      between '2014-10-09' and '2014-10-10';

SQL Select between dates

SQLite requires dates to be in YYYY-MM-DD format. Since the data in your database and the string in your query isn't in that format, it is probably treating your "dates" as strings.

How to get data between two dates from sqlite database which is in dd/mm/yyyy format

Life will be quite hard using the format dd/mm/yyyy as it's not readily usable by the most obvious SELECT using a BETWEEN clause as part of the WHERE clause, even harder when dd/mm/yyyy is often with single characters for values that are less than 10 (e.g. 1/1/2019 instead of 10/10/2019).

Consider mytable created and loaded using :-

DROP TABLE IF EXISTS mytable;
CREATE TABLE IF NOT EXISTS mytable (mydatecolumn TEXT, myothercolumn TEXT DEFAULT 'BLAH');
INSERT INTO mytable (mydatecolumn) 
    VALUES 
        ('01/01/2019'),('1/1/2019'),('01/1/2019'),('1/01/2019'),
        ('10/1/2019'),('10/10/2019'),('1/10/2019'),('01/02/2019'),
        ('1/3/2019'),('01/1/2019'),('14/01/2019'),('10/1/2019'),
        ('10/10/2020'),('1/10/2018')
; 

Which looks like :-

The query to transpose values and to then select a date range could be :-

-- An example that would hanlde dd/mm/yyyy where dd and mm could be either 1 or 2 characters
WITH 

    -- First CTE gets the day and the rest of the date
    ctedaypart AS (
        SELECT 
            rowid AS daypartid, 
            substr(mydatecolumn,1,instr(mydatecolumn,'/')-1) AS day_part, 
            substr(mydatecolumn,instr(mydatecolumn,'/')+1) AS rest_after_day 
        FROM mytable
    ),
    
    -- Second CTE gets the month and the rest of the date
    ctemonthpart AS (
        SELECT 
            daypartid AS monthpartid,
            substr(rest_after_day,1,instr(rest_after_day,'/')-1) AS month_part, 
            substr(rest_after_day,instr(rest_after_day,'/')+1) AS year 
        FROM ctedaypart
    ),

    -- Third CTE expands the day and month the have a leading 0 id less than 10 and joins the parts to form YYYY-MM-DD 
    expandedparts AS (
        SELECT
            *,
            mytable.rowid AS expandedpartsid,
          year||'-'||
            CASE WHEN length(month_part) = 1 THEN '0'||month_part ELSE month_part END ||'-'||
            CASE WHEN length(day_part) = 1 THEN '0'||day_part ELSE day_part END AS date_in_sqlite_format
        FROM mytable JOIN ctedaypart ON mytable.rowid = daypartid  JOIN ctemonthpart ON daypartid = monthpartid)
        
SELECT mytable.* FROM mytable JOIN expandedparts ON mytable.rowid = expandedpartsid WHERE (date_in_sqlite_format) BETWEEN ('2019-01-01') AND ('2019-03-31');

The above results in 10 of the 14 rows being selected as per :-

---

However

If the date is saved in the database in a recognised format e.g. YYYY-MM-DD then the above could simply be :-

SELECT * FROM mytable WHERE (mydatecolumn) BETWEEN ('2019-01-01') AND ('2019-03-31');

---

As such it is suggested that you adopt the use of a recognised format for dates when interacting with the database :-

Time Strings
A time string can be in any of the following formats:

YYYY-MM-DD
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSS
YYYY-MM-DDTHH:MM
YYYY-MM-DDTHH:MM:SS
YYYY-MM-DDTHH:MM:SS.SSS
HH:MM
HH:MM:SS
HH:MM:SS.SSS
now
DDDDDDDDDD

SQL As Understood By SQLite - Date And Time Functions

The alternative is to use or adapt the complex query above or use one that is similar.

Can't get data between two dates with different year SQLite

Change the format of your dates to 'YYYY-MM-DD' which is the only valid and comparable format in SQLite:

UPDATE TASKS_TBL  
SET START_DATE = SUBSTR(START_DATE, 7) || '-' || SUBSTR(START_DATE, 1, 2) || '-' || SUBSTR(START_DATE, 4, 2),
    DUE_DATE = SUBSTR(DUE_DATE, 7) || '-' || SUBSTR(DUE_DATE, 1, 2) || '-' || SUBSTR(DUE_DATE, 4, 2)

and your code to:

BETWEEN '2019-01-01' AND AND DATE('now') 
AND DUE_DATE <='2020-05-18'

and the query will work fine.

how to retrieve date between two dates in sqlite database

Query

insert into test (t) select '2012-01-15';

insert into test (t) select '2012-11-16';

select * from test where t between '2012-01-01' and '2012-02-01';

Fiddle demo

How to get all the records between two dates from sqlite database in flutter?

  Future<List> getRangeData(String fromDate, String toDate) async{
   var dbClient = await db;
   var result = await dbClient
        .rawQuery("SELECT * FROM $tableName where $columnDate >= '2021-01-01' and 
    $columnDate <= '2022-10-10' ");
   return result.toList();
  }

How can I get sum of count between two dates in sqlite

This worked for me, Thanks.

SELECT COUNT(*) AS Count FROM tab WHERE  DATE( SUBSTR( a.Dt,-4 )||'-'||Substr(a.Dt,1,2)||'-'||Substr(a.Dt,4,2) ) BETWEEN '2019-10-01' AND '2020-08-15';

Again this also works.

SELECT COUNT(*) AS Count FROM tab WHERE tab.date BETWEEN '20191001' AND '20200815';

---

Related Topics