display date range with no table

Question

is it possible to display date range in mysql withouth table? I have this code but it lack.

SET @startDate = '2018-10-01';
SET @endDate = '2019-02-21';

select @startDate + INTERVAL seq.seq DAY AS sequential_day
  from (
    SELECT A.N + 5*(B.N + 5*(C.N + 5*(D.N + 5*(E.N + 5*(F.N))))) AS seq
      FROM (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS A
      JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS B
      JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS C
      JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS D
      JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS E
      JOIN (SELECT 0 AS N UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS F
       ) AS seq
 where seq.seq <= @endDate

it displays from 2018-10-01 to 2024-04-11 ? there is wrong with my query, any idea? I wanted to display from 2018-10-01 to 2019-02-21

I'm using mariaDB

Possible duplicate of [How to get list of dates between two dates in mysql select query](https://stackoverflow.com/questions/9295616/how-to-get-list-of-dates-between-two-dates-in-mysql-select-query) — tshimkus, Feb 22 '19 at 19:17
Remember though that SQL isn't a panacea. There are some tasks for which it's simply inappropriate — Strawberry, Feb 22 '19 at 22:05

score 1 · Accepted Answer · answered Feb 22 '19 at 19:16

I got an answer here:

How to get list of dates between two dates in mysql select query

SET @startDate = '2018-10-01';
SET @endDate = '2019-02-21';

select * from 
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between @startDate and @endDate

That's right. You need `WHERE` to actually filter the query – tshimkus Feb 22 '19 at 19:16 — tshimkus, Feb 22 '19 at 19:16

Bernd Buffen · Answer 2 · 2019-03-09T08:15:10.040

You can use the SEQUENCE Engine like ths:

select CURDATE() + INTERVAL seq day  FROM seq_1_to_31;

Here is the full Sample:

SELECT '2018-10-01' + interval seq day as my_date FROM seq_0_to_9999
WHERE '2018-10-01' + interval seq day  <= '2019-02-21';

Details you can found here: https://mariadb.com/kb/en/library/sequence-storage-engine/

See which Engines are installed

MariaDB [test]> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                          | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| MRG_MyISAM         | YES     | Collection of identical MyISAM tables                                            | NO           | NO   | NO         |
| CSV                | YES     | Stores tables as CSV files                                                       | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                        | NO           | NO   | NO         |
| MyISAM             | YES     | Non-transactional engine with good performance and small data footprint          | NO           | NO   | NO         |
| Aria               | YES     | Crash-safe tables with MyISAM heritage                                           | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                               | NO           | NO   | NO         |
| SEQUENCE           | YES     | Generated tables filled with sequential values                                   | YES          | NO   | YES        |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.006 sec)

MariaDB [test]>

Sample

MariaDB [test]> select seq FROM seq_1_to_4;
+-----+
| seq |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
+-----+
4 rows in set (0.042 sec)

MariaDB [test]> select CURDATE() + INTERVAL seq day  FROM seq_1_to_31;
+--------------------------------+
| DATE(NOW()) + INTERVAL seq day |
+--------------------------------+
| 2019-02-23                     |
| 2019-02-24                     |
| 2019-02-25                     |
| 2019-02-26                     |
| 2019-02-27                     |
| 2019-02-28                     |
| 2019-03-01                     |
| 2019-03-02                     |
| 2019-03-03                     |
| 2019-03-04                     |
| 2019-03-05                     |
| 2019-03-06                     |
| 2019-03-07                     |
| 2019-03-08                     |
| 2019-03-09                     |
| 2019-03-10                     |
| 2019-03-11                     |
| 2019-03-12                     |
...
...
| 2019-03-22                     |
| 2019-03-23                     |
| 2019-03-24                     |
| 2019-03-25                     |
+--------------------------------+
31 rows in set (0.019 sec)

MariaDB [test]>

Use `CURDATE()` instead of `DATE(NOW())`. – Rick James Mar 08 '19 at 19:14 — Rick James, Mar 08 '19 at 19:14

score 0 · Answer 3 · answered Feb 22 '19 at 19:20

0

Use BETWEEN for range of values. As itself in your query you want dates within some range

Your current query displays all <= some_value It isnt falling in that specific range you want the dates to be in. BETWEEN allows you to give a range of values.

answered Feb 22 '19 at 19:20

Himanshu

3,830
2
10
29

display date range with no table

3 Answers3