Select date range (month / year) in database
I have a quick question regarding selecting dateranges in MySQL, formatted this way YYYY-MM-DD
I have read MySQL for date range selection and understand its basic usage, but how can I enable February 29th? I would like to avoid a PHP workaround, is there anything similar in MySQL?
I cannot understand http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date
can anyone give me an example of how to fetch data for recent years from February?
source to share
The moment you specify the month, the MYSQL engine is smart enough to get the correct number of days in 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 a year ago)
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 just simply write the YEAR function without BETWEEN
SELECT * FROM yourtable
WHERE YEAR(yourdate) = YEAR(Now()) - 1
AND MONTH(yourdate) = 2 -- to get data for Month of February
;
In the question, the Str_to_Date
function is used to convert a string containing a date to a date type with the required format and the inverse DATE_FORMAT
function.
SELECT STR_TO_DATE('12-Apr-2012', '%d-%M-%Y')
FROM DUAL
;
Note that in the above demo, you need to specify the location of the day, month, year in the format that you add to the function Str_to_Date
.
PS: it's pretty vague what you are actually trying to achieve here, without the same data and expected repetitions shown in the question .. :)
source to share