Search for next 7 days records in SQL

Asked

Viewed 458 times

3

I have the following SQL

SELECT `age`.*, `inte`.`int_nome`, `inte`.`int_cod_unidade`, `set_uni`.`uni_titulo` as set_unidade, `set_cur`.`cur_titulo` as set_curso, `set_sta`.`set_setor` as set_status, `con`.`con_nome` as agendado_por, `ope`.`con_nome` as atendido_por
FROM (`agendamentos` as age)
JOIN `interessados` as inte ON `inte`.`int_cod`=`age`.`age_cod_interessado`
JOIN `unidades` as set_uni ON `set_uni`.`uni_cod`=`inte`.`int_cod_unidade`
JOIN `cursos` as set_cur ON `set_cur`.`cur_cod`=`inte`.`int_cod_curso`
JOIN `setores` as set_sta ON `set_sta`.`set_cod`=`age`.`age_status`
LEFT JOIN `consultoras` as con ON `con`.`con_cod`=`age`.`age_cod_consultora_agendado`
LEFT JOIN `consultoras` as ope ON `ope`.`con_cod`=`age`.`age_cod_consultora_atendido`
WHERE `age`.`age_data_agendado_para` BETWEEN CURRENT_DATE() AND CURRENT_DATE()+7

I need you to return records for the next seven days, but in my database I have the following records:

inserir a descrição da imagem aqui

As I do to return correctly, in this example, I would have to return only two records that are within the criteria.

sql

2 answers

2

Change that part:

BETWEEN CURRENT_DATE()+7 AND CURRENT_DATE()

That’s why:

BETWEEN CURRENT_DATE AND CURRENT_DATE() + 7

would be from today to today more 7 days, I think Voce got confused in the order of dates

1


How are you utilizing a field of the kind DATETIME, accurate ensure that all records will return. For this concaten the beginning with ' 00:00:00' and the end with ' 23:59:59', in order to ensure the first and last moment of the day.

In this way, replace:

WHERE `age`.`age_data_agendado_para` BETWEEN CURRENT_DATE() AND CURRENT_DATE()+7

For:

WHERE `age`.`age_data_agendado_para` 
    BETWEEN
      concat(CURRENT_DATE(), ' 00:00:00') AND 
      concat(CURRENT_DATE() + INTERVAL 7 DAY, ' 23:59:59')

  • Just one remark: + INTERVAL 7 DAY put on the bottom, because it is 7 days away. And it worked.

  • 1

    I adjusted! (I did the tests with data in the past, I changed - interval by + interval, but I had forgotten the order.

  • 1

    True. Or you could take the time and just use the date, doing this: cast (age.age_data_agendado_para as date) between CURRENT_DATE() AND CURRENT_DATE()+7

  • It is that in my case, I use the timestamp for time control also, to know what time is scheduled,for this reason, I gave the correct answer of Allan

  • 1

    True @Gabriellocalhost, I would just have to test if I would change anything in performance, but I think a valid alternative too.

  • So use 'Current_timestamp', I think that would remove the need for concatenation

Show 1 more comment

Browser other questions tagged sql

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