Get List of Last 15 Days Date in SQL

Question

Could SQL get list of date of last 15 days date in a single query?

We can get today date with

select current_date()

We also can get last 15 days date with

select date_add(current_date(), -15)

But how to show the list of last 15 days date? For example the output is

2020-05-17, 
2020-05-18, 
2020-05-19, 
2020-05-20, 
2020-05-21, 
2020-05-22, 
2020-05-23, 
2020-05-24, 
2020-05-25, 
2020-05-26, 
2020-05-27, 
2020-05-28, 
2020-05-29, 
2020-05-30, 
2020-05-31

An RDBMS is for the storage and retrieval of relational data. For other things, consider using application level code. — Strawberry, Jun 02 '20 at 05:31
@Akina i've told you above. The version is 1.1.0-cdh5.16.2 rUnknown — Monana, Jun 02 '20 at 08:07
Hive does not support recursive CTE. So generate numbers pseudotable in CTE using static numbers list and apply it to "starting" date. — Akina, Jun 02 '20 at 08:13

leftjoin · Accepted Answer · 2020-06-02T10:43:12.930

2

In Hive or Spark-SQL:

select date_add (date_add(current_date,-15),s.i) as dt 
  from ( select posexplode(split(space(15),' ')) as (i,x)) s

Result:

See also this answer.

edited Jun 02 '20 at 10:43

answered Jun 02 '20 at 10:36

leftjoin

36,950
8
57
116

score 1 · Answer 2 · answered Jun 02 '20 at 09:47

WITH 
cte AS ( SELECT 1 num UNION ALL SELECT 2 UNION ALL ... UNION ALL SELECT 15 )
SELECT DATEADD(CURRENT_DATE(), -num)
FROM cte;

Or, for example

WITH 
cte1 AS ( SELECT 1 num UNION ALL 
          SELECT 2 UNION ALL 
          SELECT 3 UNION ALL 
          SELECT 4 UNION ALL 
          SELECT 5 ),
cte2 AS ( SELECT 0 num 
          UNION ALL SELECT 1 
          UNION ALL SELECT 2 )
SELECT DATEADD(CURRENT_DATE(), -cte1.num - cte2.num * 5)
FROM cte1, cte2;

Get List of Last 15 Days Date in SQL

2 Answers2