Search between dates with between

Asked

Viewed 1,514 times

7

I have the following research:

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

In the database, I have:

inserir a descrição da imagem aqui

But on my return, I can only search for two records, as the 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 anyone tell me why I can’t list all the items?

sql

3 answers

5


When you only use the date on BETWEEN, Mysql interprets it as if the time were 00:00:00.
Therefore the date 2016-08-18 09:00:30 is not between the dates of your filter, it is larger than 2016-08-18 00:00:00.

Just include the time on 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"
  • 2

    Puts "2016-08-30 23:59:59.999999"

  • 1

    Well this, I put the schedules and it worked. But I also tried to use CAST, and it worked too.

  • Okay, that’s it. But those dates at your conclusion don’t make any sense, you used the age_data_agendamento while the consultation uses the age_data_agendado_para.

  • I think CAST is a better idea. Because putting string to compare with date you let Mysql convert the way it wants.

  • age_data_scheduling = Date the scheduling was launched on the system, and age_data_scheduled for is when it was scheduled.

  • Example: The consultant Marina, launched on 15/01/2015 a schedule for André to 20/01/2015. Endenteste?

Show 1 more comment

4

Another option would be to use a CAST for DATE

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

3

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

So your 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";

Hence, 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’ve helped!

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.