CODEWITHSUNDEEP
mysql
Yassi
Yassi

Reputation: 2529

Mysql Month - Year Comparison

How can I make a query that conditions a Month - Year only selection? Like, Where the range is January 2013 - December 2014?

I have a "date" column in my table.

I usually do a date range like WHERE date >= ? AND date <= ? which is from date to date. I only want to select the Month and Year only, without the day.

Now I want to do WHERE date >= (January 2013) AND date date <= (December 2014).

Is it possible?

Upvotes: 3

Views: 9750

Answers (6)

Mario
Mario

Reputation: 196

SELECT fields
FROM table
WHERE EXTRACT(YEAR_MONTH FROM date) BETWEEN '2013-01' AND '2014-12'

You also could use DATE_FORMAT(date,'%Y%m') instead of EXTRACT.

Upvotes: 2

Curt
Curt

Reputation: 5722

SELECT * 
FROM MySampleTable
WHERE Month(create_dt) IN (1, 2, 3)
AND Year(create_dt) = 2013

If you've got a lot of data in your table, and the date field you're interested in is indexed, you might want to bound this with a between start date and end date. Evaluation of these functions will force a table scan (since the date value has to be massaged by the two functions in order to be interpreted correctly), so this will run slow. Anything you can do to reduce the number of rows being evaluated will be helpful.

Upvotes: 0

Gimmy
Gimmy

Reputation: 3911

Consider a select like this:

SELECT *
FROM supportContacts
WHERE DATE_FORMAT(date, '%Y') BETWEEN '2013' AND '2014'
AND DATE_FORMAT(date, '%m') BETWEEN '01' AND '12';

SQLFiddle

Upvotes: 1

vikrant singh
vikrant singh

Reputation: 2111

SELECT * FROM TABLE_NAME
WHERE DATE_FORMAT(date, "%Y-%m") BETWEEN '2013-01' AND '2014-12';

Upvotes: 6

Goutam Pal
Goutam Pal

Reputation: 1763

SELECT * FROM `table` WHERE date_format(`date`,'%M %Y') >= 'January 2013' AND date_format(`date`,'%M %Y') <= 'December 2014'

Upvotes: 0

bansi
bansi

Reputation: 57002

use Month and Year function of mysql Date and Time Functions

Upvotes: 1

Related Questions