SQLite database - select the data between two dates?

Question

I want to select my data by date - from a date until another date, so I have this query,

SELECT * FROM mytalbe WHERE date BETWEEN '2014-10-09' AND '2014-10-10'

But this query only return the data in '2014-10-09', excluding the data in '2014-10-10', unless I change the query to this below,

SELECT * FROM mytalbe WHERE date BETWEEN '2014-10-09' AND '2014-10-11'

This is not an ideal solution. How can I select the data including the data in '2014-10-10'?

NOTE:

I think my problem is different from other duplicate questions becos,

1. My date type is TEXT.
2. I need to select the date's data without its time.
3. It is an SQLite database.

My sample data:

    sid     nid timestamp   date    
1   20748   5   1412881193  2014-10-09 14:59:53 
2   20749   5   1412881300  2014-10-09 15:01:40 
3   20750   5   1412881360  2014-10-09 15:02:40

Answer 1

IF date is a timestamp, you'll need to do like:

SELECT * FROM mytalbe WHERE date BETWEEN '2014-10-09 00:00:00' AND '2014-10-10 23:59:59'

Or you can do, I believe:

SELECT * FROM mytalbe WHERE DATE(date) BETWEEN '2014-10-09' AND '2014-10-10'

Or, since it is a text field:

SELECT * FROM mytalbe WHERE DATE_FORMAT(date,'%Y-%m-%d') BETWEEN '2014-10-09' AND '2014-10-10'

Answer 2

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';

Answer 3

same problem solved, thanks. my code :

var FechaInicio = dtpFechaInicial.Value.ToString("yyyy-MM-dd");
var FechaFinal = dtpFechaFinal.Value.ToString("yyyy-MM-dd");

string SQLcmd = $"SELECT * FROM sorteos WHERE DATE(fecha) BETWEEN ('{FechaInicio}') AND ('{FechaFinal}')";

Answer 4

I tested this code snippet:

SELECT local_date FROM (SELECT '2014-10-09 14:59:53' local_date UNION SELECT '2014-10-09 15:01:40' UNION SELECT '2014-10-09 15:02:40') WHERE local_date BETWEEN date('2014-10-09') AND date('2014-10-10')

Which basically returned:

2014-10-09 14:59:53
2014-10-09 15:01:40
2014-10-09 15:02:40

So the query you're looking for is probably this:

SELECT * FROM mytalbe WHERE date BETWEEN date('2014-10-09') AND date('2014-10-10')

I highly recommend this official documentation: https://www.sqlite.org/lang_datefunc.html