0

i want to ask how to showing the date from first date of the month until end of date of the month for example i want to showing date from 2021-01-01 until 2021-01-30. i want join this data with my other table.

iam using MYSQL V5.7

i want this result ::

| 2019-01-01 |
| 2019-01-02 |
| 2019-01-03 |
| 2019-01-04 |
| 2019-01-05 |
| .......... |
| .......... |
| 2019-01-31 |

i try using this Query but doesn't work

SELECT DATE(DATE_SUB(CURRENT_DATE, INTERVAL DAYOFMONTH(CURRENT_DATE)-1 DAY) BETWEEN DATE(NOW()))

Thanks for the answer

Fajar Donny Bachtiar
  • 140
  • 6

2 Answers2

2

If your question is how to return all dates for a single month, you can use a recursive CTE:

with recursive dates as (
      select date('2021-01-01') as dte
      union all
      select dte + interval 1 day
      from dates
      where dte < last_day(dte)
     )
select *
from dates;

This can easily be extended to other date ranges by changing the where condition.

Here is a db<>fiddle.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
-1

Old versions of MySQL cannot generate rows that are not stored in a table. I always find myself building a "dates" table to get this done.

You can use this process:

DELIMITER $$
CREATE PROCEDURE fill_dates_table(start_date DATE, end_date DATE)
BEGIN
  DECLARE d DATE;
  
  CREATE TABLE IF NOT EXISTS dates (
    date DATE NOT NULL PRIMARY KEY
  );
  SET d = start_date;
  WHILE d <= end_date DO
    INSERT IGNORE INTO dates VALUES (d);
    SET d = d + INTERVAL 1 DAY;
  END WHILE;
END $$
DELIMITER ;

Execute this procedure:

CALL fill_dates_table('2019-01-01', '2019-01-31');

Then, use the dates table as you see fit.

If you're using version 8, consider Gordon's answer.

Barranka
  • 20,547
  • 13
  • 65
  • 83