Solved

# YTD report dates in oracle

Posted on 2013-01-08
614 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
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 3
• 3
• +1

LVL 74

Accepted Solution

sdstuber earned 250 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 250 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 22

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.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any othâ€¦
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platformsâ€¦
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to take different types of Oracle backups using RMAN.
###### Suggested Courses
Course of the Month5 days, 17 hours left to enroll