CODEWITHSUNDEEP
sqloracle-databasedatetimetype-conversion
PedroEstevesAntunes
PedroEstevesAntunes

Reputation: 91

Return the last six months of a current date (ORACLE)

Good afternoon.

I would like to get a list of the last six months before the current date.

Example.

Current Date -> 10/02/2020

Format of output pretended in the select Oracle Query:

02-2020 -> (Current)
01-2020
12-2019
11-2019
10-2019
09-2019

Best regards and many thanks

Upvotes: 0

Views: 2325

Answers (2)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

To answer your subquestion about ordering - to order anything in SQL you need to use an ORDER BY clause. In this case you can't order the formatted dates easily, but you can use the LEVEL value generated by the CONNECT BY:

SELECT TO_CHAR(ADD_MONTHS(SYSDATE, 1 - LEVEL), 'MM-YYYY')
  FROM DUAL
  CONNECT BY LEVEL <= 6
  ORDER BY LEVEL DESC

Upvotes: 1

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

One way is to use connect by:

select to_char(add_months(sysdate, -level + 1), 'mm-yyyy') mth from dual connect by level <= 6

Upvotes: 3

Related Questions