0

How would I go about retrieving records from the last three months of the previous year? I was thinking it would be:

 Date_add(curdate() , interval '-1 2' year_month)
user2966637
  • 11
  • 5

2 Answers2

1

Try this:

WHERE my_date_column BETWEEN
  SUBDATE(CURDATE(), INTERVAL DAYOFYEAR(CURDATE()) + 91 DAY) AND
  SUBDATE(CURDATE(), INTERVAL DAYOFYEAR(CURDATE()) DAY)

91 is one less than 31 + 30 + 31, because BETWEEN is inclusive.

Note that if your column is a datetime type, you'll need the end value to be the last second of the day:

SUBDATE(SUBDATE(CURDATE(), INTERVAL DAYOFYEAR(CURDATE()) - 1 DAY), INTERVAL 1 SECOND)

See SQLFiddle of these expressions generating correct values.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
-1

Assuming you date column is named "date", something like:

SELECT 
*
FROM 
table

WHERE
YEAR(date) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR))

AND
MONTH(date) BETWEEN 10 AND 12
Sébastien
  • 11,860
  • 11
  • 58
  • 78
  • Logically valid but otherwise not good, since you're defeating any possibility of using an index with this query by using columns as input arguments to functions. You need to derive constant datetime values like was done in the accepted answer so that the optimizer can satisfy this with a range scan on the index on the date column if such an index exists (or is added in the future). The apparent convolution involved in that answer is for a very good reason. – Michael - sqlbot Nov 11 '13 at 03:57