如何当月的1日和当天在MySQL之间进行选择？(How do I select between th

我需要从MySQL数据库当前的月份和当前日期的第一天之间进行选择的数据。

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

有人可以提供此查询的工作的例子吗？

Answer 1:

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

或更好 ：

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

Answer 2:

我一直在寻找的地方，我需要用一个月的第一天，我的查询类似的查询。 该last_day功能并没有为我工作，但DAYOFMONTH派上了用场。

所以，如果有人找了同样的问题，下面的代码返回当月的第一天的日期。

SELECT DATE_SUB(CURRENT_DATE, INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY);

比较每月的第一天的日期栏：

select * from table_name where date between 
DATE_SUB(CURRENT_DATE, INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY) and CURRENT_DATE

Answer 3:

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

SQLFiddle例子

Answer 4:

我用下面的查询。 这为我在过去的工作很大。

select date(now()) - interval day(now()) day + interval 1 day

Answer 5:

试试这个 ：

SET @StartDate = DATE_SUB(DATE(NOW()),INTERVAL (DAY(NOW())-1) DAY);
SET @EndDate = ADDDATE(CURDATE(),1);
select * from table where (date >= @StartDate and date < @EndDate);

Answer 6:

MySQL的当月和当年的完整解决方案，这使得使用索引的正确以及:)

-- 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 7:

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

Answer 8:

一个不太正统的方法可能是

SELECT * FROM table_name 
WHERE LEFT(table_name.date, 7) = LEFT(CURDATE(), 7)
  AND table_name.date <= CURDATE();

为日后的第一个月的现在之间，相当于一个日期在这一个月之中，和之前现在。 我不觉得这是对眼睛比其他一些方法更容易一点，虽然。

Answer 9:

select * from table
where date between 
(date_add (CURRENT_DATE, INTERVAL(1 - DAYOFMonth(CURRENT_DATE)) day)) and current_date;

Answer 10:

SELECT date_sub(current_date(),interval dayofmonth(current_date())-1 day) as first_day_of_month;

Answer 11:

我用这一个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

Answer 12:

我有一些什么类似的要求-要find first day of the month ，但根据year end month的用户在他们的个人资料页面中选择。

问题陈述-找到所有txns用户在他/她的财政年度完成。 Financial year是用确定的year end一个月值，其中month可以是任何有效的月份- 1 for Jan ， 2 for Feb ， 3 for Mar .... 12 for Dec 。

对于一些客户financial year end S上March和一些观察它在December 。

Scenarios - (Today is `08 Aug, 2018`)
   1. If `financial year` ends on `July` then query should return `01 Aug 2018`.
   2. If `financial year` ends on `December` then query should return `01 January 2018`.
   3. If `financial year` ends on `March` then query should return `01 April 2018`.
   4. If `financial year` ends on `September` then query should return `01 October 2017`.

并且，最后下面是查询。 -

select @date := (case when ? >= month(now()) 
then date_format((subdate(subdate(now(), interval (12 - ? + month(now()) - 1) month), interval day(now()) - 2 day)) ,'%Y-%m-01')
else date_format((subdate(now(), interval month(now()) - ? - 1 month)), '%Y-%m-01') end)

在那里? 是year end月份（值从1到12）。

Answer 13:

SET @date:='2012-07-11';
SELECT date_add(date_add(LAST_DAY(@date),interval 1 DAY), 
       interval -1 MONTH) AS first_day

文章来源: How do I select between the 1st day of the current month and current day in MySQL?