CODEWITHSUNDEEP
mysqlsqldate
Ms01
Ms01

Reputation: 4702

Two date columns, one date. How can I get the data?

I have the following problem:

In mysql I have a table which contains two date columns start_date and end_date. The date format is yyyy-mm-dd. What I am trying to do is to get all data from all the rows where a specific date, lets say '2012-03-05' mateches one of these date columns or are something in between.

How can I create a good sql-query that gets the data needed? I've checked on the between statement but I don't really know if that's the best way to go. I guess this is generally a simple task but I just can't figure a good query out.

Thanks.

Upvotes: 1

Views: 216

Answers (4)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726659

This is a very common way to structure your tables with ranges of dates, especially in temporal database designs. It lets you perform range-based queries very efficiently, assuming that indexes on both columns exist. You query the data like this:

select *
from mytable t
where t.start_date <= @desired_date and t.endDate > @desired_date

@desired_date is the date for which you would like to query, e.g. '2012-03-05'.

Note the <= on one side and > on the other side, without =. This is done to ensure that the from-to ranges define non-overlapping intervals.

Upvotes: 3

Philip Sheard
Philip Sheard

Reputation: 5825

SELECT * FROM mytable
WHERE '2012-03-05' BETWEEN start_date AND end_date;

Upvotes: 2

halfer
halfer

Reputation: 20429

Not sure, try something like this:

SELECT
    *
FROM
    mytable
WHERE
    '2012-03-05' BETWEEN start_date AND end_date

Upvotes: 2

Corbin
Corbin

Reputation: 33447

SELECT * FROM table WHERE start_date <= '2012-02-29' AND end_date >= '2012-02-29';

Should do it.

Upvotes: 4

Related Questions