CODEWITHSUNDEEP
phpmysqldate
Zabs
Zabs

Reputation: 14142

MySQL - Between months (rather than a timestamp)

how would I generate a MySQL query to grab a row that is between a particular month eg grab row where month is between 01/01 (1st Jan) and 01/06 (1st June)

Upvotes: 1

Views: 22972

Answers (5)

DerVO
DerVO

Reputation: 3679

I'm not sure, if you want to grab all rows from Jan to June, only the rows until June 1st or all rows from Jan to May. You can use for example:

[...] WHERE `date_column` BETWEEN '2012-01-01' AND '2012-06-01'

which gives you all rows including June 1st.

Use this to get all rows from the full months of Jan to June:

[...] WHERE YEAR(`date_column`)=2012 AND MONTH(`date_column`) BETWEEN 1 AND 6

(change to BETWEEN 1 AND 5 for Jan to May)

Upvotes: 10

applechief
applechief

Reputation: 6895

You can use the month(), year() and day() functions. So your query would look like something like

SELECT * FROM table WHERE month(date_field) >= 1 AND month(date_field) <= 6 AND year(date_field) = 2011

date_field should be a date or datetime field, if it is a timestamp, you need to use the FROM_UNIXTIMESTAMP function so it would look like month(FROM_UNIXTIMESTAMP(timestamp_field))

Upvotes: 1

Minras
Minras

Reputation: 4346

If you generate your SQL query in PHP, you can use BETWEEN

$date = date("Y") . '-' . date("m") . '-01';
$sql = "SELECT * 
FROM `table`
WHERE `date_column` BETWEEN '$date' AND '$date' + INTERVAL 1 MONTH - INTERVAL 1 DAY";

(I subtracted one day to exclude the data of the next month first day)

Or you can use EXTRACT

$month = 5;
$sql = 'SELECT * 
FROM `table`
WHERE EXTRACT(MONTH FROM `date_column`) = ' . $month;

Upvotes: 4

calumbrodie
calumbrodie

Reputation: 4792

SELECT
  *
FROM
  yourtable
WHERE
  date BETWEEN '2012-01-01' AND '2012-06-01';

Upvotes: 0

capi
capi

Reputation: 1453

Assuming the column in mysql is the right format (date/datetime) you can simply compare (>,<) :

SELECT somecolumn 
FROM sometable
WHERE datecolumn >= '2012-01-01' AND datecolumn < '2012-06-01'

Hope it helps!

Upvotes: 2

Related Questions