Search for the next 7 days in SQL [duplicate]

Navigation
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 to return records for the next 7 days, however, in my database I have the following records:

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

    
asked by anonymous 30.08.2016 / 15:42

2 answers

1

Since you are using a field of type DATETIME , you need to ensure that all records will return. For this, concatenate the beginning with '00:00:00' and the end with '23:59:59', in order to guarantee 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

By: 

WHERE 'age'.'age_data_agendado_para' 
    BETWEEN
      concat(CURRENT_DATE(), ' 00:00:00') AND 
      concat(CURRENT_DATE() + INTERVAL 7 DAY, ' 23:59:59')
    
30.08.2016 / 15:55
2

Change this part: 

BETWEEN CURRENT_DATE()+7 AND CURRENT_DATE()

So: 

BETWEEN CURRENT_DATE AND CURRENT_DATE() + 7

It would be from today until today 7 more days, I think you got confused in the order of the dates

    
30.08.2016 / 15:54
Align form items with CSS How to see the queries that were executed by eloquent in Laravel?