Let’s say you want all rows from a table that have a date between next month and the next 3 months, MySQL provide us (since 4.1) a beautiful LAST_DAY function (for the record there is no FIRST_DAY…. at least yet), and the DATE_ADD function (actually there is also a DATE_SUB).
Getting first day of next month:
SELECT DATE_ADD(LAST_DAY(NOW()), INTERVAL 1 DAY);Getting the last day of the next third calendar month
SELECT LAST_DAY(DATE_ADD(NOW(), INTERVAL 3 MONTH));So, from here is pretty simple:
SELECT * FROM table WHERE
date_field > DATE_ADD(LAST_DAY(NOW()), INTERVAL 1 DAY) AND
date_field < LAST_DAY(DATE_ADD(NOW(), INTERVAL 3 MONTH));Bonus query:
get first day of current month
SELECT CAST(DATE_FORMAT(NOW() ,'%Y-%m-01') as DATE)
dude…
there are certain requirements for this query to work(not that it is query’s fault)…..it is found out that the field should be “date” type or else the query wont return anything….if u have any work-around for extracting the same above query on a field-type “text” pls do mention it…it would be really helpful.
Thanks man….the above query is helping a lot in other projects.
Hi,
have you tried casting to date? If the text data is in the yyyy-mm-dd format it should work fine:
SELECT * FROM table WHERE
CAST( text_field AS DATE ) > DATE_ADD(LAST_DAY(NOW()), INTERVAL 1 DAY) AND
CAST( text_field AS DATE ) < LAST_DAY(DATE_ADD(NOW(), INTERVAL 3 MONTH)); Really don't know about the casting overhead, but i think that if the table isn't huge it should run pretty decently.
Hi…
actually i tried with the data type as “text” first ….it didnt work.
Then, i created another field with datatype “date”…values kept the same…fired the query….it did work.
Also, the format was as you had mentioned(actually i tried all the formats, but that didnt work)
My conclusion: The query works only on data-type “date”.
If u find my conclusion incorrect do reply back….i wud like to get a proper query which works on all data types. 🙂