[mysql] How do I select between the 1st day of the current month and current day in MySQL?

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?

This question is related to mysql date get

The answer is


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() )

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

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);

I was looking for a similar query where I needed to use the first day of a month in my query. The last_day function didn't work for me but DAYOFMONTH came in handy.

So if anyone is looking for the same issue, the following code returns the date for first day of the current month.

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

Comparing a date column with the first day of the month :

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

I found myself here after needing this same query for some Business Intelligence Queries I'm running on an e-commerce store. I wanted to add my solution as it may be helpful to others.

set @firstOfLastLastMonth = DATE_SUB(LAST_DAY(DATE_ADD(NOW(), INTERVAL -2 MONTH)),INTERVAL DAY(LAST_DAY(DATE_ADD(NOW(), INTERVAL -2 MONTH)))-1 DAY);
set @lastOfLastLastMonth = LAST_DAY(DATE_ADD(NOW(), INTERVAL -2 MONTH));
set @firstOfLastMonth = DATE_SUB(LAST_DAY(DATE_ADD(NOW(), INTERVAL -1 MONTH)),INTERVAL DAY(LAST_DAY(DATE_ADD(NOW(), INTERVAL -1 MONTH)))-1 DAY);
set @lastOfLastMonth = LAST_DAY(DATE_ADD(NOW(), INTERVAL -1 MONTH));
set @firstOfMonth = DATE_ADD(@lastOfLastMonth, INTERVAL 1 DAY);
set @today = CURRENT_DATE;

Today is 2019-10-08 so the output looks like

@firstOfLastLastMonth = '2019-08-01'
@lastOfLastLastMonth = '2019-08-31'
@firstOfLastMonth = '2019-09-01'
@lastOfLastMonth = '2019-09-30'
@firstOfMonth = '2019-10-01'
@today = '2019-10-08'

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

In your case, since you need to compare the value with other DATE values in your table, it doesn't really matter what methodology you use because MySQL will do the conversion implicitly even if your formula doesn't return a DATE object.

So really, take your pick. Which is most readable for you? I'd pick the first since it's the shortest and arguably the simplest:

SELECT * FROM table_name 
WHERE date BETWEEN CONCAT(LEFT(CURRENT_DATE, 7), '-01') AND CURDATE;

SELECT * FROM table_name 
WHERE date BETWEEN DATE(CONCAT(LEFT(CURRENT_DATE, 7), '-01')) AND CURDATE;

SELECT * FROM table_name 
WHERE date BETWEEN (LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY - INTERVAL 1 MONTH) AND CURDATE;

SELECT * FROM table_name 
WHERE date BETWEEN (DATE(CURRENT_DATE) - INTERVAL (DAYOFMONTH(CURRENT_DATE) - 1) DAY) AND CURDATE;

SELECT * FROM table_name 
WHERE date BETWEEN (DATE(CURRENT_DATE) - INTERVAL (DAYOFMONTH(CURRENT_DATE)) DAY + INTERVAL 1 DAY) AND CURDATE;

SELECT * FROM table_name 
WHERE date BETWEEN DATE_FORMAT(CURRENT_DATE,'%Y-%m-01') AND CURDATE;

A less orthodox approach might be

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

as a date being between the first of a month and now is equivalent to a date being in this month, and before now. I do feel that this is a bit easier on the eyes than some other approaches, though.


try this :

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);

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

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

I have used the following query. It has worked great for me in the past.

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

I had some what similar requirement - to find first day of the month but based on year end month selected by user in their profile page.

Problem statement - find all the txns done by the user in his/her financial year. Financial year is determined using year end month value where month can be any valid month - 1 for Jan, 2 for Feb, 3 for Mar,....12 for Dec.

For some clients financial year ends on March and some observe it on 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`.

And, finally below is the query. -

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)

where ? is year end month (values from 1 to 12).


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

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

All the responses here have been way too complex. You know that the first of the current month is the current date but with 01 as the date. You can just use YEAR() and MONTH() to build the month date by inputting the NOW() method. Here's the solution:

select * from table_name 
where date between CONCAT_WS('-', YEAR( NOW() ), MONTH( NOW() ), '01') and DATE( NOW() )

CONCAT_WS() joins a series of strings with a separator (a dash in this case). So if today is 2020-08-28, YEAR( NOW() ) = '2020' and MONTH( NOW() ) = '08' and then you just need to append '01' at the end.

Voila!


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

SQLFiddle example


Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to date

How do I format {{$timestamp}} as MM/DD/YYYY in Postman? iOS Swift - Get the Current Local Time and Date Timestamp Typescript Date Type? how to convert current date to YYYY-MM-DD format with angular 2 SQL Server date format yyyymmdd Date to milliseconds and back to date in Swift Check if date is a valid one change the date format in laravel view page Moment js get first and last day of current month How can I convert a date into an integer?

Examples related to get

Getting "TypeError: failed to fetch" when the request hasn't actually failed java, get set methods For Restful API, can GET method use json data? Swift GET request with parameters Sending a JSON to server and retrieving a JSON in return, without JQuery Retrofit and GET using parameters Correct way to pass multiple values for same parameter name in GET request How to download HTTP directory with all files and sub-directories as they appear on the online files/folders list? Curl and PHP - how can I pass a json through curl by PUT,POST,GET Making href (anchor tag) request POST instead of GET?