CODEWITHSUNDEEP
mysql
PrimuS
PrimuS

Reputation: 2683

Select a date range (Month/Year) in database

I have a quick question regarding selecting dateranges in MySQL, formatted like this YYYY-MM-DD

I read MySQL select date range issue and understand the basic usage of it, but how can I "include" the 29th of february for example? I'd like to avoid a PHP workaround, is there anything like that in MySQL?

I can't quite understand http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date

can someone give me an example how to select last years data from february?

Upvotes: 3

Views: 26724

Answers (2)

bonCodigo
bonCodigo

Reputation: 14361

The moment you specify a month, MYSQL engine is smart enough to get the correct number of days for a month (whether it's a leap year or not) and evaluate your date fields accordingly.

To get data between two dates of your choice: (using interval -1 year to get one year back from today)

SELECT * FROM yourtable 
WHERE yourdate BETWEEN DATE_ADD(Now(), Interval -1 YEAR)
AND Now() 
AND MONTH(yourdate) = 2 -- to get data for Month of February
;

Or you can simply just write with YEAR function without BETWEEN

SELECT * FROM yourtable 
WHERE YEAR(yourdate) = YEAR(Now()) - 1
AND MONTH(yourdate) = 2 -- to get data for Month of February
;

For the question on Str_to_Date the function is used to convert string that contains a date into a date type with a desired format and it's the inverse of DATE_FORMAT function. 

SELECT STR_TO_DATE('12-Apr-2012', '%d-%M-%Y')
FROM DUAL
;

Notice in the above demo that you have to specify the day, month, year arrangement in the format you are adding into the STR_TO_DATE function.

PS: it's rather vague what you are really tryhing to achieve here, without sameple data and expected reslts shown in the question.. :)

Upvotes: 7

Minesh
Minesh

Reputation: 2302

Below query will give you data of last year February month

SELECT * FROM `your_table_name` WHERE YEAR(`your_date_field_name`) = YEAR(CURRENT_DATE()) AND MONTH(`your_date_field_name`) = 2

Upvotes: 3

Related Questions