Use BETWEEN together with IN()

Asked

Viewed 103 times

-1

I would like to compare several dates within a range of dates. I tried to perform the query below but I cannot find a proper logic.

Consultation with Array + BETWEEN in Mysql, or rather use the IN() along with BETWEEN.

I’ve tried a few ways but you can’t

1)

SELECT
  *
FROM
  Conteudo
WHERE
  ('2018-03-22', '2018-03-23') BETWEEN DATE(DataInicio) AND DATE(DataFim)

2)

SELECT
      *
    FROM
      Conteudo
    WHERE
      BETWEEN DATE(DataInicio) AND DATE(DataFim) IN('2018-03-22','2018-03-23' )
mysql database

3 answers

2

It is "possible", but to do this you need to isolate the expressions:

SELECT
    *
FROM
    Conteudo
WHERE
    campo_data BETWEEN DATE(DataInicio) AND DATE(DataFim) 
    AND campo_data IN('2018-03-22','2018-03-23')

  • If there is a date field, it really works that way, but if dates are entered manually, it wouldn’t work that way.

  • didn’t understand, @arllondias. There has to be a date field, otherwise you won’t have to check at the base

  • It is not necessary one more field, thinks that it already has the fields that in the case of query of him he informs that he has the DataInicio and DataFim. If he puts a date in his hand and leaves so for example: WHERE '2018-03-22' BETWEEN DATE(DataInicio) AND DATE(DataFim) He’s gonna bring me the records where the date fits between the dates he already has in the bank, you understand? exactly as I put in my answer there.

  • @arllondias, got it. So your answer is correct :P

  • 1

    I added a functional example in my reply =)

1

Well, as far as I know this is not possible, but you can go adding OR conditions in your code instead of IN, there would be more or less this way:

SELECT
  *
FROM
  Conteudo
WHERE
  '2018-03-22' BETWEEN DATE(DataInicio) AND DATE(DataFim)
  OR '2018-03-23' BETWEEN DATE(DataInicio) AND DATE(DataFim)
...

Follow example with tests: 

CREATE TABLE teste_datas(
    descricao VARCHAR(255), 
    data_inicio DATE, 
    data_fim DATE
);

INSERT INTO teste_datas(descricao, data_inicio, data_fim)
VALUES('desc 1', '2018-03-01', '2018-03-22'),
('desc 1', '2018-03-01', '2018-03-23'),
('desc 2', '2018-03-01', '2018-03-24'),
('desc 3', '2018-03-01', '2018-03-08'),
('desc 4', '2018-03-01', '2018-03-02');`

In case I run this query:

SELECT
    *
FROM teste_datas
WHERE '2018-03-22' BETWEEN data_inicio AND data_fim;

The result will be desc1, desc2 e desc3 because the date I’m looking for fits between the two periods

inserir a descrição da imagem aqui

SELECT
    *
FROM teste_datas
WHERE '2018-03-22' BETWEEN data_inicio AND data_fim
OR '2018-03-07' BETWEEN data_inicio AND data_fim;

if I add the OR to query will return to me desc1, desc2, desc3 e desc4 because the second date also fits a line.

inserir a descrição da imagem aqui

-1

It is possible, as follows.

The only however is if you want to var a OR as in the example lowered or a AND.

SELECT * FROM Conteudo
WHERE (campo_data BETWEEN DataInicio AND DataFim)
OR (campo_data in ('2018-03-22','2018-03-23'))

Browser other questions tagged mysql database

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