Search records from one date to another

Question

The company has the base closure of "21 to 20" each month. Would you like to know how I make SQL only search for the records that are inside the lock? Ex: my closing this month is: 21/05 to 20/06, but I do not want to take the last 30 days... I need to search the records within a certain period.

Answer 1

André, I created an example in Sqlfiddle for you to take a look here.

Sqlfiddle Demo

In this example, I created an example table tb_exemplo and entered 4 values:

• Value 1, Date '2015-05-19'
• Value 2, Date '2015-05-21'
• Value 3, Date '2015-06-10'
• Value 4, Date '2015-06-22'

To search for ranges the ideal is to use the operator BETWEEN, could even make a data > x and data < y, but it is unnecessary since we have another option.

I don’t know if you’ll work this data AFTER the SELECT then you will probably need the function DATE_FORMAT to display the date the way you need it.

Example cited in Sqlfiddle:

SELECT *,date_format(campo_data,'%d/%m/%Y') as `data_formatada` FROM tb_exemplo
WHERE campo_data BETWEEN '2015-05-21' AND '2015-06-20'

Exit

valor campo_data              data_formatada

2     May, 21 2015 00:00:00   21/05/2015

3     June, 10 2015 00:00:00  10/06/2015

UPDATE

-

Sqlfiddle Demo #2

SELECT *,date_format(campo_data,'%d/%m/%Y') as `data_formatada` FROM tb_exemplo
WHERE (DAY(NOW()) < 21 && campo_data BETWEEN DATE_SUB(CAST(DATE_FORMAT(NOW() ,'%Y-%m-21') as DATE), INTERVAL 1 MONTH)
                                         AND CAST(DATE_FORMAT(NOW() ,'%Y-%m-20') as DATE))
   OR (DAY(NOW()) >= 21 && campo_data BETWEEN CAST(DATE_FORMAT(NOW() ,'%Y-%m-21') as DATE)
                                         AND DATE_ADD(CAST(DATE_FORMAT(NOW() ,'%Y-%m-20') as DATE), INTERVAL 1 MONTH));

Explanation

• NOW() - Returns the current date
• DAY() - Pull the day out, so I can tell if it’s past the 21st or not
• DATE_SUB() - Subtract date in case I step INTERVAL 1 MONTH, that is, I will SUBTRACT 1 month.
• DATE_ADD() - Add date in case I step INTERVAL 1 MONTH, I mean, I’ll ADD 1 month.