Search data between a period in a table with start date and end date fields

Asked

Viewed 479 times

0

I’m having a problem searching for data in a table with start date and end date fields between a period (a - up filter)

The sample data are:

ID - INICIO     - FIM
1  - 01/01/2017 - 31/12/2017
2  - 10/01/2017 - 01/02/2017
3  - 01/05/2017 - 01/06/2017
4  - 15/05/2017 - 15/06/2017
5  - 01/09/2017 - 31/10/2017

Hence I have 3 searches and with expected returns

1st search between 01/01/2017 - 31/12/2017
expected return - 1-2-3-4-5

2nd search between 01/04/2017 - 30/06/2017
expected return - 1-3-4

3rd search between 02/06/2017 - 02/07/2017
expected return - 1-4

The query I did for the first search was: http://sqlfiddle.com/#! 9/ba8d19/2

select * from dados
where
    dt_inicio >= '2017-01-01' AND dt_inicio <= '2017-12-31'
AND dt_fim >= '2017-01-01' AND dt_fim <= '2017-12-31'
// o resultado foi ok

The query I made for the second search was: http://sqlfiddle.com/#! 9/ba8d19/3

select * from dados
where
    dt_inicio >= '2017-04-01' AND dt_inicio <= '2017-06-30'
AND dt_fim >= '2017-04-01' AND dt_fim <= '2017-06-30'
// o resultado FALHOU

The query I made for the third search was: http://sqlfiddle.com/#! 9/ba8d19/4

select * from dados
where
    dt_inicio >= '2017-06-02' AND dt_inicio <= '2017-07-02'
AND dt_fim >= '2017-06-02' AND dt_fim <= '2017-07-02'
// o resultado FALHOU

Follow script to create table and data:

CREATE TABLE IF NOT EXISTS `dados` (
`id` int(11) NOT NULL,
  `dt_inicio` date NOT NULL DEFAULT '0000-00-00',
  `dt_fim` date NOT NULL DEFAULT '0000-00-00'
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;


INSERT INTO `dados` (`id`, `dt_inicio`, `dt_fim`) VALUES
(1, '2017-01-01', '2017-12-31'),
(2, '2017-01-10', '2017-02-01'),
(3, '2017-05-01', '2017-06-01'),
(4, '2017-05-15', '2017-06-15'),
(5, '2017-01-09', '2017-10-31')
sql date

1 answer

0


After a discussion in other groups, I reached a solution, follows:

SELECT * FROM dados
WHERE 
    dt_inicio <= '$dt_fim_busca'
AND dt_fim >= '$dt_inicio_busca'

With the examples given to $dt_inicio_busca and $dt_fim_busca

$dt_inicio_busca | $dt_fim_busca
--------------------------------
01/01/2017       | 31/12/2017
01/04/2017       | 30/06/2017
02/06/2017       | 02/07/2017

Browser other questions tagged sql date

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