Select MySQL Based Only on Month and Year

How to select mySQL records based only on month and year without full table scan

You are using YEAR() and MONTH() function in where clause when using a function in where clause MySQL doesn't use the index of that column to executing your query.

You can change your query to below and make a try

SELECT id, created FROM table_name WHERE created between '2020-05-01 00:00:00' and '2020-05-31 23:59:59'

** Don't forget to add an index to your field.

MySQL: How to do date between by year and month (no date)

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)

Get data based on year and month

You only want to compare months for first and last years:

  WHERE (tt.year > 2014 AND tt.year < 2018)
    or  (tt.year = 2014 AND tt.month_number >= 5)
    or  (tt.year = 2018 AND tt.month_number <= 5)

Alternatively:

WHERE (tt.year * 100 + tt.month_number >= 201405)
  AND (tt.year * 100 + tt.month_number <= 201805)

Or, not supported by MySQL:

WHERE (tt.year, tt.month_number) between (2014,5) and (2018,5)

Adapted to MySQL by Raymond Nijland:

WHERE (tt.year, tt.month_number) >= (2014,5) AND (tt.year, tt.month_number) <= (2018,5)

SQL Query to select data based on year and month

To find records of some month, update your where clause to :

where CONCAT(YEAR(date),'-',MONTH(date)) = '2017-12'

Retrieve MySql Records Within a Specific Month but Less than the current year and Less than the current Day

Use the functions DAY(), MONTH() and YEAR() in your conditions:

SELECT * 
FROM my_table 
WHERE MONTH(date) = MONTH(CURRENT_DATE)
  AND YEAR(date) < YEAR(CURRENT_DATE) 
  AND DAY(date) < DAY(CURRENT_DATE);

See the demo.

Select by Month of a field

Simply use MONTH() and YEAR():

SELECT * FROM Project WHERE MONTH(DueDate) = 1 AND YEAR(DueDate) = 2010

How can I filter rows according to a date range when only year, month and day are available?

Just to clarify, this is a way to do it, but your problem is your DBA, your arquitechture, etc. etc. You won't solve this by this way, neither the time or resources wasted. Maybe you need to ask a proper way to do this with non SQL database in DBA stack exchange site.

Anyway, for the to-know way:

1. Convert data to datestamp ISO: yyyyMMdd with CONCAT and LPAD
2. Compare the data as normal integer

Example: (of course you can change the values of your search)

SELECT * from dt WHERE CONCAT(year,LPAD(month, 2, '0'),LPAD(day, 2, '0')) BETWEEN 20201231 AND 20210101

---

Related Topics