Search between dates with between [duplicate]

Navigation
7

I have the following search: 

SELECT * FROM ('agendamentos') 
WHERE 'age_data_agendado_para' BETWEEN "2016-08-28" and "2016-08-30"

In the database, I have:

But on my return, I can only search for two records, as print_r (): 

Array - Campos de Pesquisa
(
    [age_senha] => 
    [age_nome] => 
    [age_cod_consultora_atendido] => 
    [age_cod_consultora_agendado] => 
    [age_data_inicial] => 2016-08-28
    [age_data_final] => 2016-08-30
    [age_status] => 
)
Array - Resultados Print_r
(
    [0] => stdClass Object
        (
            [age_cod] => 6
            [age_cod_interessado] => 2
            [age_data_agendamento] => 2016-08-18 09:00:12
            [age_cod_consultora_agendado] => 1
            [age_cod_consultora_atendido] => 1
            [age_data_agendado_para] => 2016-08-29 08:00:00
            [age_senha] => MA2745
            [age_status] => 50
        )

    [1] => stdClass Object
        (
            [age_cod] => 7
            [age_cod_interessado] => 2
            [age_data_agendamento] => 2016-08-18 09:00:30
            [age_cod_consultora_agendado] => 1
            [age_cod_consultora_atendido] => 1
            [age_data_agendado_para] => 2016-08-29 08:00:00
            [age_senha] => MA2740
            [age_status] => 50
        )

)

Can someone tell me why I can not list all the items?

    
asked by anonymous 18.08.2016 / 14:59

3 answers

5

When you only use the date in BETWEEN , MySQL interprets it as if the time was 00:00:00 . Therefore the 2016-08-18 09:00:30 date is not between the dates of your filter, it is greater than 2016-08-18 00:00:00 .

Just include the time in the filter that should already work: 

SELECT * FROM ('agendamentos') 
WHERE 'age_data_agendado_para' BETWEEN "2016-08-28 00:00:00" and "2016-08-30 23:59:59.999999"
    
18.08.2016 / 15:07
4

Another option would be to use CAST to DATE 

SELECT * FROM ('agendamentos') WHERE CAST(age_data_agendado_para as DATE)
BETWEEN "2016-08-28" and "2016-08-30"
    
18.08.2016 / 15:12
3

The record is not returned because the age_data_agendado_para field is of type datetime and the dates passed in its BETWEEN are of type date .

Thus, its SELECT can be read as follows: 

SELECT * FROM ('agendamentos') WHERE 'age_data_agendado_para' BETWEEN "2016-08-28 00:00:00.000000" and "2016-08-30 00:00:00.000000";

So the date 2016-08-30 08:00:00 is out of range.

The most appropriate correction would be: 

SELECT * FROM ('agendamentos') WHERE 'age_data_agendado_para' BETWEEN "2016-08-28 00:00:00.000000" and "2016-08-30 23:59:59.999999";

or, if possible, a cast could solve more elegantly: 

SELECT * FROM ('agendamentos') WHERE CAST(age_data_agendado_para as DATE) BETWEEN "2016-08-28" and "2016-08-30";

I hope I have helped!

    
18.08.2016 / 15:09
What are the differences between Kernel and Micro-Kernel? Load parts list using AngularJS