CODEWITHSUNDEEP
sqloracle-databaseoracle11g
avg998877
avg998877

Reputation: 127

dates of past 12 months in oracle

Need a query that lists all the dates of the past 12 months

say my current date is 10-21-2013. need to use sysdate to get the data

The result should look like

10/21/2013
...
10/01/2013
09/30/2013
...
09/01/2013
...
01/31/2013
...
01/01/2013
...
11/30/2012
...
11/01/2012

Please help me with this.. Thanks in advance.

AVG

Upvotes: 1

Views: 5719

Answers (3)

BWS
BWS

Reputation: 3836

You could do something like this:

select to_date('21-oct-2012','dd-mon-yyyy') + rownum -1
from all_objects
where rownum <= 
    to_date('21-oct-2013','dd-mon-yyyy') - to_date('21-oct-2012','dd-mon-yyyy')+1

of course, you could use parameters for the start and end date to make it more usable.

-or- using sysdate, like this:

select sysdate + interval '-1' year + rownum -1
from all_objects
where rownum <= 
    sysdate - (sysdate + interval '-1' year)

Upvotes: 1

David Aldridge
David Aldridge

Reputation: 52346

Allowing for leap years and all, by using add_months to work out the date 12 months ago and thus how many rows to generate ...

select trunc(sysdate) - rownum + 1 the_date
from   dual
connect by level <= (trunc(sysdate) - add_months(trunc(sysdate),-12))

Upvotes: 3

Teja
Teja

Reputation: 13534

Try this

SELECT SYSDATE,
       SYSDATE-1,
       <continue>   
       SYSDATE-30,         
       <continue>
        .........
       <continue>    
       SYSDATE-364,
       SYSDATE-365

FROM DUAL;

Upvotes: 0

Related Questions