CODEWITHSUNDEEP
mysqlsql
user8927599
user8927599

Reputation:

Query dates that is lesser or equal to set of date ranges

How do I query dates that is lesser than or equal to more than one dates with single sql query?

I need to find 

date <= ('29-05-2018', '30-05-2018', '31-05-2018')

Thanks in advance.

Upvotes: 1

Views: 98

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520948

The most restrictive date, i.e. the earliest one, will determine the filtering, since any dates greater than this would be less restrictive. So, an alternative to using ALL would be to use LEAST:

WHERE date <= LEAST('2018-05-29', '2018-05-30', '2018-05-31')

EDIT (by Gordon):

In Oracle, this would be:

WHERE date <= LEAST(DATE '2018-05-29', DATE '2018-05-30', DATE '2018-05-31')

Upvotes: 7

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

You could use ALL:

Compares a value to every value in a list or returned by a query. Must be preceded by =, !=, >, <, <=, >=. Can be followed by any expression or subquery that returns one or more values.

date <= ALL('29-05-2018', '30-05-2018', '31-05-2018');

Is it possible to have hardcoded dates in the sub query for ALL operator?

Yes it is possible:

SELECT *
FROM tab_name
WHERE date <= ALL (SELECT some_date_column
                   FROM tab_name_2
                   WHERE ...);

<=>
SELECT *
FROM tab_name
WHERE date <= (SELECT MIN(some_date_column)
               FROM tab_name_2
               WHERE ...);

I don't have have those dates in the table. I just want to find out the dates which lies from today to (today + 30 days)

SELECT *
FROM table_name
WHERE date BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE) + 30; -- Oracle

Upvotes: 5

Related Questions