CODEWITHSUNDEEP
sqloracle-database
Sulejmani
Sulejmani

Reputation: 11

Oracle get last 10 days sql

I need to get the last 10 days from now on I currently have following Oracle sql query to get the last 10 dates.

WITH DATES AS (
  SELECT sysdate - 10 AS value_date FROM dual
  UNION
  SELECT sysdate - 9  AS value_date FROM dual
  UNION
  SELECT sysdate - 8  AS value_date FROM dual
  UNION
  SELECT sysdate - 7  AS value_date FROM dual
  UNION
  SELECT sysdate - 6  AS value_date FROM dual
  UNION
  SELECT sysdate - 5  AS value_date FROM dual
  UNION
  SELECT sysdate - 4  AS value_date FROM dual
  UNION
  SELECT sysdate - 3  AS value_date FROM dual
  UNION
  SELECT sysdate - 2  AS value_date FROM dual
  UNION
  SELECT sysdate - 1  AS value_date FROM dual
  UNION
  SELECT sysdate      AS value_date FROM dual
  UNION
  SELECT sysdate + 1  AS value_date FROM dual

But I want a more proper and efficient way to get the past 10 dates. An Union query looks pretty dirty. How do I do this in Oracle SQL?

Upvotes: 3

Views: 8046

Answers (2)

krokodilko
krokodilko

Reputation: 36087

Use a more compact equivalent with CONNECT BY clause:

SELECT trunc( sysdate ) - level + 2
FROM dual
CONNECT BY rownum <= 12

Demo: http://sqlfiddle.com/#!4/a50e25/5

| TRUNC(SYSDATE)-LEVEL+2 |
|------------------------|
|   2017-09-15T00:00:00Z |
|   2017-09-14T00:00:00Z |
|   2017-09-13T00:00:00Z |
|   2017-09-12T00:00:00Z |
|   2017-09-11T00:00:00Z |
|   2017-09-10T00:00:00Z |
|   2017-09-09T00:00:00Z |
|   2017-09-08T00:00:00Z |
|   2017-09-07T00:00:00Z |
|   2017-09-06T00:00:00Z |
|   2017-09-05T00:00:00Z |
|   2017-09-04T00:00:00Z |

Upvotes: 5

MT0
MT0

Reputation: 167822

You can get the same results as your query using a hierarchical query (also using TRUNC() to truncate the date to midnight at the start of the day):

SELECT TRUNC( SYSDATE ) - 11 + LEVEL
FROM   DUAL
CONNECT BY LEVEL <= 12

or, use a recursive sub-query factoring clause:

WITH days ( dt ) AS (
  SELECT TRUNC( SYSDATE ) - 10 FROM DUAL
UNION ALL
  SELECT dt + 1 FROM days WHERE dt <= SYSDATE
)
SELECT dt FROM days

Upvotes: 1

Related Questions