CODEWITHSUNDEEP
mysqldate
empugandring
empugandring

Reputation: 571

MySQL show all date from entire a month

how can i show all date from entire a month, where month is parameter for query (for example month between 1 and 3) then output this query will show date from first date in january until last date on march. maybe preview like below.

2013-01-01
2013-01-02
2013-01-03
..
..
2013-02-28
..
2013-03-31

have any idea for this?thanks for your help

Upvotes: 7

Views: 16587

Answers (7)

Jan Pavlik
Jan Pavlik

Reputation: 21

Since MySQL 8.0 (or MariaDB 10.2.2) can be used Recursive Common Table Expressions:

WITH RECURSIVE `days` AS
(
   SELECT 1 AS `day` UNION ALL SELECT `day` + 1 FROM `days` WHERE `day` < DAY(LAST_DAY(NOW()))
)
SELECT * FROM `days`

Upvotes: 1

CalvT
CalvT

Reputation: 3213

I've tweaked Brent Frere's answer to automatically list all the days for the current month, with no hardcoding of the date:

select FROM_UNIXTIME(UNIX_TIMESTAMP(CONCAT(year(now()),'-',month(now()),'-',n)),'%Y-%m-%d') as Date from (
        select (((b4.0 << 1 | b3.0) << 1 | b2.0) << 1 | b1.0) << 1 | b0.0 as n
                from  (select 0 union all select 1) as b0,
                      (select 0 union all select 1) as b1,
                      (select 0 union all select 1) as b2,
                      (select 0 union all select 1) as b3,
                      (select 0 union all select 1) as b4 ) t
        where n > 0 and n <= day(last_day(now()))
order by Date

Alternatively, if you wanted to set the year and month using MySQL variables:

set @year = 2020, @month = 11;
select FROM_UNIXTIME(UNIX_TIMESTAMP(CONCAT(@year,'-',@month,'-',n)),'%Y-%m-%d') as Date from (
        select (((b4.0 << 1 | b3.0) << 1 | b2.0) << 1 | b1.0) << 1 | b0.0 as n
                from  (select 0 union all select 1) as b0,
                      (select 0 union all select 1) as b1,
                      (select 0 union all select 1) as b2,
                      (select 0 union all select 1) as b3,
                      (select 0 union all select 1) as b4 ) t
        where n > 0 and n <= day(last_day(CONCAT(@year,'-',@month,'-',1)))
order by Date

Upvotes: 2

Brent Fr&#232;re
Brent Fr&#232;re

Reputation: 21

As example, for december 2019:

select FROM_UNIXTIME(UNIX_TIMESTAMP(CONCAT('2019-12-',n)),'%Y-%m-%d') as Date from (
        select (((b4.0 << 1 | b3.0) << 1 | b2.0) << 1 | b1.0) << 1 | b0.0 as n
                from  (select 0 union all select 1) as b0,
                      (select 0 union all select 1) as b1,
                      (select 0 union all select 1) as b2,
                      (select 0 union all select 1) as b3,
                      (select 0 union all select 1) as b4 ) t
        where n > 0 and n <= day(last_day('2019-12-01'))

It produces the following result:

+------------+
| Date       |
+------------+
| 2019-12-01 |
| 2019-12-02 |
| 2019-12-03 |
| 2019-12-04 |
| 2019-12-05 |
| 2019-12-06 |
| 2019-12-07 |
| 2019-12-08 |
| 2019-12-09 |
| 2019-12-10 |
| 2019-12-11 |
| 2019-12-12 |
| 2019-12-13 |
| 2019-12-14 |
| 2019-12-15 |
| 2019-12-16 |
| 2019-12-17 |
| 2019-12-18 |
| 2019-12-19 |
| 2019-12-20 |
| 2019-12-21 |
| 2019-12-22 |
| 2019-12-23 |
| 2019-12-24 |
| 2019-12-25 |
| 2019-12-26 |
| 2019-12-27 |
| 2019-12-28 |
| 2019-12-29 |
| 2019-12-30 |
| 2019-12-31 |
+------------+
31 rows in set (0.00 sec)

Upvotes: 2

Alin Stoian
Alin Stoian

Reputation: 1142

If you already have a table with number of entries greater than the number of dates that you need, then you can use this:

SELECT adddate('2013-01-01', @rownum := @rownum + 1) dt FROM my_table
JOIN (SELECT @rownum := -1) r
LIMIT 31;

Choose a small table for my_table. If my_table contains too many entries, use limit to make it faster.

The basis of this is using a counter like rownum that autoincrements itself. A more complex query with filtering using date functions:

SELECT * FROM 
    (SELECT adddate('2013-01-01', @rownum := @rownum + 1) dt FROM my_table
    JOIN (SELECT @rownum := -1) r LIMIT 1000) temp
WHERE MONTH(dt) = 1 AND YEAR(dt) = 2015;

Upvotes: 5

peterm
peterm

Reputation: 92795

If you need to generate a list of all dates between two dates you can do that with the help of tally (numbers) table. You can easily create one like this

CREATE TABLE tally (n int not null primary key);

INSERT INTO tally
SELECT a.N + b.N * 10 + c.N * 100 + 1 n
  FROM 
 (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c
ORDER BY n;

Now to create a list of all dates between the first of January and the last day of March you can do this

SELECT '2013-01-01' + INTERVAL t.n - 1 DAY day
  FROM tally t
 WHERE t.n <= DATEDIFF(LAST_DAY('2013-03-01'), '2013-01-01') + 1

Here is SQLFiddle demo

Upvotes: 1

Nickolai Nielsen
Nickolai Nielsen

Reputation: 942

SELECT Field1, Field2 FROM table WHERE dateField between '2013-01-01' and '2013-03-31'

Upvotes: 0

juergen d
juergen d

Reputation: 204784

select * from your_table
where month(`date`) between 1 and 3

And if you need it for a specific year you should add that

select * from your_table
where year(`date`) = 2013
and month(`date`) between 1 and 3

Upvotes: 0

Related Questions