Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 409
  • Last Modified:

Oracle date question

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
anumoses
Asked:
anumoses
  • 3
  • 2
  • 2
  • +1
1 Solution
 
johnsoneSenior Oracle DBACommented:
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
 
anumosesAuthor Commented:
If I dont need the time stamp then will this be corect?

TO_DATE(TO_CHAR(COLL_DATE, 'MMDD')||'2012', 'MMDDYYYY')
0
 
johnsoneSenior Oracle DBACommented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
awking00Commented:
Another way -
to_date('2012','yyyy') + (coll_date - trunc(coll_date,'yy'))
0
 
slightwv (䄆 Netminder) Commented:
>>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
 
awking00Commented:
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
 
johnsoneSenior Oracle DBACommented:
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
 
anumosesAuthor Commented:
thanks
0
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.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now