CODEWITHSUNDEEP
oracle-databasedate
Chris Sell
Chris Sell

Reputation: 23

Oracle SQL - Last 12 full months of data

I need a query that will dynamically pull the last 12 full months of shipping data (excluding current month). So with today being September 30, 2016, I would need data from September 1, 2015 to August 31, 2016. Tomorrow, the query would change to the date range of 10-1-15 to 9-30-16.

Here is what I have currently:

WHERE (shipdate BETWEEN TRUNC(sysdate, 'Year') AND sysdate)

This query pulls data from the beginning of the calendar year up to TODAY's date instead of the previous 12 completed months. I have found answers for this with MySQL and MS SQL Server but not for Oracle. How can this be accomplished in Oracle?

Upvotes: 2

Views: 42279

Answers (2)

user5683823
user5683823

Reputation: 

between add_months(trunc(sysdate, 'month'), -12) and trunc(sysdate, 'month')

If a shipment may actually be timestamped at exactly midnight and shipments with a timestamp of Sept. 1 at 00:00:00 should not be included, then "between" should be changed to 

shipdate >= add_months(trunc(sysdate, 'month'), - 12) 
                               and shipdate < trunc(sysdate, 'month')

Upvotes: 6

Aleksej
Aleksej

Reputation: 22949

Say you have a table with a list of dates, for example last 1000 days:

create table tableTest(shipDate) as
(
    select trunc(sysdate) - level +1
    from dual
    connect by level < 1000
)

you can use the following:

select min(shipdate), max(shipdate)
from tableTest
where shipDate between trunc(add_months(sysdate, -12), 'MONTH') and TRUNC(sysdate, 'MONTH') -1

Upvotes: 1

Related Questions