How do I select between the 1st day of the current

Question

I need to select data from MySQL database between the 1st day of the current month and current day.

select*from table_name 
where date between "1st day of current month" and "current day"

Can someone provide working example of this query?

Answer 1

The key here is to get the first day of the month. For that, there are several options. In terms of performance, our tests show that there isn't a significant difference between them - we wrote a whole blog article on the topic. Our findings show that what really matters is whether you need the result to be VARCHAR, DATETIME, or DATE.

The fastest solution to the real problem of getting the first day of the month returns VARCHAR:

SELECT CONCAT(LEFT(CURRENT_DATE, 7), '-01') AS first_day_of_month;

The second fastest solution gives a DATETIME result - this runs about 3x slower than the previous:

SELECT TIMESTAMP(CONCAT(LEFT(CURRENT_DATE, 7), '-01')) AS first_day_of_month;

The slowest solutions return DATE objects. Don't believe me? Run this SQL Fiddle and see for yourself

查看更多

0人赞 添加讨论(0) 举报

Answer 2

select * from table_name 
where `date` between curdate() - dayofmonth(curdate()) + 1
                 and curdate()

SQLFiddle example

Answer 3

select * from <table>
where <dateValue> between last_day(curdate() - interval 1 month + interval 1 day)
                  and curdate();

Answer 4

Complete solution for mysql current month and current year, which makes use of indexing properly as well :)

-- Current month
SELECT id, timestampfield 
  FROM table1
 WHERE timestampfield >= DATE_SUB(CURRENT_DATE, INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY)
   AND timestampfield <=  LAST_DAY(CURRENT_DATE);

-- Current year
SELECT id, timestampfield 
  FROM table1
 WHERE timestampfield >= DATE_SUB(CURRENT_DATE, INTERVAL DAYOFYEAR(CURRENT_DATE)-1 DAY)
   AND timestampfield <=  LAST_DAY(CURRENT_DATE);

Answer 5

select * from table_name 
where (date between  DATE_ADD(LAST_DAY(DATE_SUB(CURDATE(), interval 30 day), interval 1 day) AND CURDATE() )

Or better :

select * from table_name 
where (date between  DATE_FORMAT(NOW() ,'%Y-%m-01') AND NOW() )

Answer 6

I used this one

select DATE_ADD(DATE_SUB(LAST_DAY(now()), INTERVAL  1 MONTH),INTERVAL  1 day) first_day
      ,LAST_DAY(now()) last_day, date(now()) today_day