Solved

Oracle date question

Posted on 2014-01-31
8
392 Views
Last Modified: 2014-02-04
i HAVE A QUERY

   select nvl(sum(recovered_shp_liters_qty), 0)recovered_shp_qty
     from pla_quantities
    where blood_center_key = 2
      AND RECOVERED_SHP_LITERS_QTY <> 0
      and process_date between :p_start_date and :coll_date;

I am doing a graph. For the above period I am getting the graph. Now I need to compare the graph for the data for 2012. No matter what date range they enter I have to go back to 2012.

I can hardcode p_start_date to be '01-jan-2012'
but coll_date is the one I get from the table to do year to date graph. My question is based on what the coll_date is can we get that to go back to 2012 ?
Can I use a decode or case statement to go back to 2012 of coll_date?


  and coll_date has to be
0
Comment
Question by:anumoses
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 34

Accepted Solution

by:
johnsone earned 250 total points
Comment Utility
Assuming that coll_date could be any year, then this should give the same date in 2012

to_date(to_char(coll_date, 'mmddhh24miss')||'2012', 'mmddhh24missyyyy')
0
 
LVL 6

Author Comment

by:anumoses
Comment Utility
If I dont need the time stamp then will this be corect?

TO_DATE(TO_CHAR(COLL_DATE, 'MMDD')||'2012', 'MMDDYYYY')
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
Yes.  coll_date has a time portion to it (all dates do), so I was just copying what was there.  If the time portion is 00:00:00 in the original, that that will just get copied.  No harm.
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
Another way -
to_date('2012','yyyy') + (coll_date - trunc(coll_date,'yy'))
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>Another way -

Seems to drop a day from time to time (unless I have a typo):
select to_date('2012','yyyy') + (to_date('10/10/2013') - trunc(to_date('10/10/2013'),'yy')) from dual;
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
Yeah, the problem here is that 2012 is a leap year. The difference in days of a non-leap year date and the first of that year will not produce the same day for a leap year unless the date is on or before February 28. One could create a case statement to deal with that, of course, but that might perform worse that converting to character and back to date again. :-(
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
Well, if you want to get fancy, then you could do this:

add_months(coll_date, (2012 - to_number(to_char(coll_date, 'yyyy')))*12)

Still has some conversions to it though.

It would be best to stay away from using the "day" math.  Almost impossible to account for leap years, as you have seen.
0
 
LVL 6

Author Closing Comment

by:anumoses
Comment Utility
thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now