How to do a select between dates [duplicate]

Navigation
1

My question is very simple, I want to make a SELECT between dates, of a given table, where status='1' .

That is, I have for example: 

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

I want to make a select of all results between these dates where status = 1

How can I do this?

    
asked by anonymous 13.01.2017 / 22:25

2 answers

2

Depends on the format in your database.

If it is in the 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 the format DATE : 

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

If it is in the 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')
    
13.01.2017 / 22:46
2

You can use multiple filters in WHERE , just put AND between them.

For an interval between two dates, use BETWEEN .

Remember that dates need to be in quotation marks and in the format yyyy-mm-dd.

Example below: 

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

Note, this is 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 do not want to worry about setting the time (@Bacco suggestion): 

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.

    
13.01.2017 / 22:30
fill input from right to left BoletoNet | Error generating shipping file