How to select between dates

Question

My question is quite simple, I intend to ask a SELECT between dates, from a given table, where the status='1'.

I mean, I have for example:

$data1 = 13/01/2017
$data2 = 20/01/2017

I want to select all results between these dates where the status=1

How can I do?

Answer 1

You can use multiple filters on WHERE, just put AND among them.

For an interval between two dates, use BETWEEN.

Remember that dates need to be in quotes and in yyyy-mm-dd format.

Example below:

SELECT *
FROM TABELA
WHERE 
    status='1'
    and data between '2017-01-13' and '2017-01-20';

Note, this if your database field is set to DATE.

If you are on DATETIME, you have to use '00:00:00' for the first date '23:59:59' for the second.

SELECT *
FROM TABELA
WHERE 
    status='1'
    and data between '2017-01-13 00:00:00' and '2017-01-20 23:59:59';

If it is in DATETIME format and you don’t want to worry about putting the time (suggestion by @Bacco):

SELECT *
FROM TABELA
WHERE 
    status='1'
    and DATE( data ) between '2017-01-13' and '2017-01-20';

If it is in TIMESTAMP format, you can use UNIX_TIMESTAMP as suggested by @Inkeliz.

Answer 2

It depends on the format that is in your database.

If it is in format DATETIME:

Use DATE():

SELECT * FROM Tabela WHERE Status = 1 AND DATE(Data) > '2017-01-01' AND DATE(Data) < '2017-01-17'

Use 00:00:00:

SELECT * FROM Tabela WHERE Status = 1 AND Data > '2017-01-01 00:00:00' AND Data < '2017-01-17 00:00:00'

If it is in format DATE:

SELECT * FROM Tabela WHERE Status = 1 AND Data > '2017-01-01' AND Data < '2017-01-17'

If it is in format TIMESTAMP/INT:

Use UNIX_TIMESTAMP():

SELECT * FROM Tabela WHERE Status = 1 AND Data > UNIX_TIMESTAMP('2017-01-01') AND Data < UNIX_TIMESTAMP('2017-01-17')