Solved

Oracle date question

Posted on 2014-01-31
8
398 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
[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
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 35

Accepted Solution

by:
johnsone earned 250 total points
ID: 39824135
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
ID: 39824157
If I dont need the time stamp then will this be corect?

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

Expert Comment

by:johnsone
ID: 39824176
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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 32

Expert Comment

by:awking00
ID: 39824915
Another way -
to_date('2012','yyyy') + (coll_date - trunc(coll_date,'yy'))
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39825000
>>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 32

Expert Comment

by:awking00
ID: 39825047
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 35

Expert Comment

by:johnsone
ID: 39825149
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
ID: 39832315
thanks
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

729 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