How do I select between the 1st day of the current month and current day in MySQL

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

User · Answer

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

User · Answer

SELECT date sub current date   interval dayofmonth current date   -1 day  as first day of month

User · Answer

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

User · Answer

select   from table where date between   date add  CURRENT DATE  INTERVAL 1 - DAYOFMonth CURRENT DATE   day   and current date

User · Answer

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

User · Answer

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

User · Answer

select   from  lt table gt  where  lt dateValue gt  between last day curdate   - interval 1 month   interval 1 day                    and curdate

User · Answer

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  gt   DATE SUB CURRENT DATE  INTERVAL DAYOFMONTH CURRENT DATE -1 DAY     AND timestampfield  lt    LAST DAY CURRENT DATE    -- Current year SELECT id  timestampfield    FROM table1  WHERE timestampfield  gt   DATE SUB CURRENT DATE  INTERVAL DAYOFYEAR CURRENT DATE -1 DAY     AND timestampfield  lt    LAST DAY CURRENT DATE

User · Answer

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

User · Answer

A less orthodox approach might be  SELECT   FROM table name  WHERE LEFT table name date  7    LEFT CURDATE    7    AND table name date  lt   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

User · Answer

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

User · Answer

select   from table name  where  date  between curdate   - dayofmonth curdate      1                  and curdate     SQLFiddle example

User · Answer

try this    SET  StartDate   DATE SUB DATE NOW    INTERVAL  DAY NOW   -1  DAY   SET  EndDate   ADDDATE CURDATE   1   select   from table where  date  gt    StartDate and date  lt   EndDate

User · Answer

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

User · Answer

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

User · Answer

SET  date   2012-07-11   SELECT date add date add LAST DAY  date  interval 1 DAY           interval -1 MONTH  AS first day

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

Examples related to mysql

Examples related to date

Examples related to get