CODEWITHSUNDEEP
sqloracle-database
A.DS
A.DS

Reputation: 246

Creating daterange in SQL

Given the date range:

'20180504' and '20180425'

I want to write a query that would return the following dates

'20180504'
'20180503'
'20180502'
'20180501'
'20180430'
'20180429'
'20180428'
'20180427'
'20180426'
'20180425'

Could anyone suggest what would be the best way to generate dates like these? The date format should be same as above, because I would use it to extract data from another table. Thanks!

Upvotes: 0

Views: 51

Answers (2)

MT0
MT0

Reputation: 168256

You can use a hierarchical query:

SQL Fiddle

Query 1:

SELECT TO_CHAR( DATE '2018-04-25' + LEVEL - 1, 'YYYYMMDD' ) AS value
FROM   DUAL
CONNECT BY DATE '2018-04-25' + LEVEL - 1 <= DATE '2018-05-04'

Results:

|    VALUE |
|----------|
| 20180425 |
| 20180426 |
| 20180427 |
| 20180428 |
| 20180429 |
| 20180430 |
| 20180501 |
| 20180502 |
| 20180503 |
| 20180504 |

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1270431

You seem to want a string output, so you can generate the dates and then convert to strings:

with dates as (
      select date '2018-04-25' + level - 1 as dte
      from dual
      connect by date '2018-04-25' + level - 1 <= date '2018-05-04'
     )
select to_char(dte, 'YYYYMMDD')
from dates;

Here is a rextester.

Upvotes: 4

Related Questions