17

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
Improve this question
10
  • what field type is "date"? Commented Apr 30, 2015 at 15:29
  • 1
    '2014-10-11' means midnight of the 10th. As in the first minute of the 10th. To be inclusive of the 10th you either need to add the time (11:59) or make it the 11th. Commented Apr 30, 2015 at 15:30
  • the date type is TEXT... Commented Apr 30, 2015 at 15:32
  • 2
    date type is TEXT MAKES NO SENSE do you mean your dates are stored as varchars? Commented Apr 30, 2015 at 15:45
  • 2
    I understand, it happens. Well, if you do get different date types, you can make them regular dates with DATE_FORMAT(date,'%m-%d-%Y') Commented Apr 30, 2015 at 15:46

4 Answers 4

Reset to default
29

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'
Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

That second query is the right answer if you add your conversion to a datetime comment from above
Thanks for the update. I get this error though Error in sqliteSendQuery(con, statement, bind.data) : error in statement: no such function: DATE_FORMAT
... sqlite? why is the question tagged with mysql
but this works anyway - SELECT * FROM mytalbe WHERE DATE(date) BETWEEN '2014-10-09' AND '2014-10-10'
you could have mentioned from the very beginning that you also had a timestamp field :/
|
13

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';
Improve this answer

15 Comments

Thanks. I have tried it but I still don't get the data in '2014-10-10'. I think the problem is the date type is TEXT...
@teelou Then can you please post your table data in the question, because as you can see from the above example, the query works as expected for the dates in a text field
this answer does the same thing with a datetime type -- you want the OP to cast to Date
@teelou it essentially means unix time - ie, treat the timestamp as seconds since the unix epoch, which is 1970-01-01 00:00:00
@teelou no problems. i'm guessing at least one of the answers here solved your question so you should probably accept one of them
|
2

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}')";
Improve this answer

Comments

0

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.