Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

mysql: how to do date between by year and month (no date)

Tags:

database

mysql

If there are 2 columns in mysql database: year; month, now I want to do a sum calculation based a year-month range without specifying the date. Let's say 2010-11 to 2011-07, how can I realize it?

SELECT * FROM TT WHERE F1 BETWEEN '2010-11' AND '2011-07'

It doesn't work.

like image 368
Héléna Avatar asked Dec 25 '22 13:12

Héléna


1 Answers

If you want to take all rows from 2010-11 to 2011-07, until the first day of August:

SELECT * FROM `table` 
WHERE `date_column` BETWEEN '2010-11-01' AND '2011-08-01'

Use this query if you want to get all rows from the full months of January to June:

SELECT * FROM `table`
WHERE YEAR(`date_column`)=2011 AND MONTH(`date_column`) BETWEEN 1 AND 6

If you want to use different years, then write different queries for each year:

SELECT * FROM `table` 
WHERE 
  (YEAR(`date_column`)=2010 AND MONTH(`date_column`) BETWEEN 11 AND 12) OR 
  (YEAR(`date_column`)=2011 AND MONTH(`date_column`) BETWEEN 1 AND 7)
like image 69
kakajan Avatar answered Jan 18 '23 22:01

kakajan