CODEWITHSUNDEEP
mysqlsqldate
CodeFanatic
CodeFanatic

Reputation: 11474

SQL query to select rows where the year is between two dates

Given a Table with from_date date and a to_date date column, I want to select all sets of data where an year, for example 2007, is the year of one of the dates or both or is in between the two dates.

How can i achieve this using mySQL?

Table:

create Table Articleprice
(`ArtikelpreisNR` int, `ArtikelNR` int, `from_Date` date, `to_date` date, `price` int);

Example Testdata

insert into Articleprice
(`ArtikelPreisNR`, `ArtikelNR`,`from_Date`, `to_Date` ,`price`)

values (1, 1, '2006-7-04', '2008-7-04', 10);

SQL: This is returning only the ones, when from_date and to_date = 2007

SELECT
MIN(price)
FROM  Articleprice
WHERE Articleprice.from_Date >= '2007/01/01' and Articleprice.to_Date <= '2007/12/31';

What do I need to do in order to solve my Case?

Upvotes: 0

Views: 1430

Answers (2)

Salman Arshad
Salman Arshad

Reputation: 272006

First you build complete dates using the year. For 2007, the dates would be 2007-01-01 and 2007-12-31 (assuming that end dates are inclusive).

Next you use overlapping dates query to find all rows that (i) end inside (ii) start inside (iii) contain or (iv) contained within the year 2007. The query is simply:

SELECT * FROM Articleprice
WHERE '2007-12-31' >= from_Date AND to_date > '2007-01-01'

Upvotes: 1

John Conde
John Conde

Reputation: 219794

Assuming your dates are actually dates and not strings, you need to use the proper date format in your query: YYYY-MM-DD not YYY/MM/DD:

WHERE Articleprice.from_Date >= '2007-01-01' and Articleprice.to_Date <= '2007-12-31';

Upvotes: 1

Related Questions