CODEWITHSUNDEEP
mysqlsql
w3father
w3father

Reputation: 569

How to select a date range of current month in mysql table?

I want to select current month rows in table

I have tried this

SELECT * FROM table WHERE YEAR(date) = YEAR(CURDATE()) AND MONTH(date) = MONTH(CURDATE())

But it is not working.

Upvotes: 1

Views: 8414

Answers (2)

indago
indago

Reputation: 2101

here is the simplest method which i have tried and works well, you want to select rows where date_field is in this month.

SELECT * FROM your_table WHERE date_field > (NOW() - INTERVAL 1 MONTH);

the above will return all records that the date_field is this month in mysql

Upvotes: 0

Tom H
Tom H

Reputation: 47402

I'm not an expert on the MySQL engine, but my guess is that you'd be better off in general by comparing the column(s) to actual date values instead of using the date functions that you have. Once you wrap the column(s) in a date function it will make indexes on the column(s) useless.

I don't have a MySQL engine on this machine to test with, but here's some pseudocode:

SELECT
    <column list>
FROM
    My_Table
WHERE
    my_date >= <get 1st of current month>(CURDATE()) AND
    my_date <  <get 1st if next month>(CURDATE())

Upvotes: 3

Related Questions