How do I extract Month and Year in a MySQL date and compare them?

Question

How do I extract the month and date from a mySQL date and compare it to another date?

I found this MONTH() but it only gets the month. I looking for month and year.

Title

There is a YEAR function too. – Lentissimo

@Hollyhock DATE_FORMAT() worked perfectly. I'd give you the correct answer. Would you mind explaining the difference between %c, %d and %e? %c worked fine but the last 2 gave me an incorrect date. – Title

DATE_FORMAT(date,'%Y%m') works as well as EXTRACT(YEAR_MONTH FROM date) – Sheepish

Answer 1

in Mysql Doku: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_extract

SELECT EXTRACT( YEAR_MONTH FROM `date` ) 
FROM `Table` WHERE Condition = 'Condition';

Discerning

Can it please be explained when EXTRACT is preferred versus DATE_FORMAT? – Benzoyl

This works well in mysql, but not in Java, use DATE_FORMAT in java – Volitive

Answer 2

While it was discussed in the comments, there isn't an answer containing it yet, so it can be easy to miss. DATE_FORMAT works really well and is flexible to handle many different patterns.

DATE_FORMAT(date,'%Y%m')

To put it in a query:

SELECT DATE_FORMAT(test_date,'%Y%m') AS date FROM test_table;

Hoarhound

Answer 3

If you are comparing between dates, extract the full date for comparison. If you are comparing the years and months only, use

SELECT YEAR(date) AS 'year', MONTH(date) AS 'month'
 FROM Table Where Condition = 'Condition';

Eyebright

Answer 4

SELECT * FROM Table_name Where Month(date)='10' && YEAR(date)='2016';

Aleasealeatory

Answer 5

You may want to check out the mySQL docs in regard to the date functions. http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

There is a YEAR() function just as there is a MONTH() function. If you're doing a comparison though is there a reason to chop up the date? Are you truly interested in ignoring day based differences and if so is this how you want to do it?

Sefton

Im trying to get the month and year for now. – Title

Answer 6

There should also be a YEAR().

As for comparing, you could compare dates that are the first days of those years and months, or you could convert the year/month pair into a number suitable for comparison (i.e. bigger = later). (Exercise left to the reader. For hints, read about the ISO date format.)

Or you could use multiple comparisons (i.e. years first, then months).

Brigid

Dates can be compared without math :-) – Hollyhock

oh yeah, let me add that too. – Brigid

Answer 7

In MySQL, dateformat() could come handy here.

Example: For a column 'date' with example datetime: 12-01-2023

DATE_FORMAT(date,'%Y-%m') 

will yield: 12-2023

Protuberance