x
Solved

# YTD report dates in oracle

Posted on 2013-01-08
Medium Priority
657 Views
select TRUNC(TO_DATE(sysdate),'YEAR'),
from dual

1/1/2013|12/31/2012 11:59:59 PM

_____________________

I need to run a year to date report.
In January 2013 I need the date parameters from 1/1/2012 to 12/31/2012
In feb 2013 I need the date parameters from 1/1/2013 to 1/31/2013

In december 2013 from 1/1/2013 to 11/30/2012
In Jan 2014 from 1/1/2013 to 12/31/2013.

Any help appreciated
0
Question by:anumoses
• 4
• 3
• 3
• +1

LVL 74

Accepted Solution

sdstuber earned 1000 total points
ID: 38756519
for results to the last day...

SELECT TRUNC(ADD_MONTHS(SYSDATE, -1), 'yyyy'), TRUNC(SYSDATE, 'mm') - 1 FROM DUAL

for results to the last second...

SELECT TRUNC(ADD_MONTHS(SYSDATE, -1), 'yyyy'), TRUNC(SYSDATE, 'mm') - 1/86400 FROM DUAL
0

LVL 74

Assisted Solution

sdstuber earned 1000 total points
ID: 38756529
simple verification code...

SELECT d, TRUNC(ADD_MONTHS(d, -1), 'yyyy'), TRUNC(d, 'mm') - 1 / 86400
FROM (SELECT     ADD_MONTHS(TO_DATE('20120615', 'yyyymmdd'), LEVEL) d
FROM DUAL
CONNECT BY LEVEL <= 24)
0

LVL 6

Author Comment

ID: 38756534
On feb 2nd if I want to run from 01-jan-2013 to 31-jan-2013, will the same query work?

Thanks,
0

LVL 23

Expert Comment

ID: 38756539
Post here that talks about finding dates based upon current / previous / next value of year, month etc.
http://programmerslounge.blogspot.com/2011/07/how-to-get-first-day-and-last-day-of.html

You seem to always need the first day of the year represented by the previous month
If you're in January, you want the 1st day of the query to be January 1 of the previous year:

select TRUNC(ADD_MONTHS(SYSDATE, -1) , 'Year') from dual;

That would give you midnight of the first day of the year.

To get the last day of the previous month at 23:59:59 you could take the first day of the current month -1 second:

select TRUNC(SYSDATE , 'Month')-1/86400 from dual;

Running those two queries today (Jan 8, 2013) I get

2012-01-01 00:00:00
2012-12-31 23:59:59
0

LVL 6

Author Comment

ID: 38756541
Oh that helped a lot. Thanks,
0

LVL 74

Expert Comment

ID: 38756560
>>> on feb 2nd if I want to run from 01-jan-2013 to 31-jan-2013, will the same query work?

yes, adjust the verification query to test whatever dates you want

it will correctly handle leap years too
0

LVL 6

Author Closing Comment

ID: 38756605
thanks
0

LVL 32

Expert Comment

ID: 38756697
select * from yourtable where datefield between
from dttbl;
0

LVL 32

Expert Comment

ID: 38756704
Didn't mean to copy "from dttbl;" (my test table)

select * from yourtable where datefield between
0

LVL 74

Expert Comment

ID: 38756712
why the decode?

from my original post is sufficient for all months
try the verification code in my second post to confirm
0

LVL 32

Expert Comment

ID: 38756735
I was a little late noticing it wasn't necessary and didn't bother changing since your solution had already been accepted by the time I posted :-)
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.