anumoses
asked on
Oracle date question
i HAVE A QUERY
select nvl(sum(recovered_shp_lite rs_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
select nvl(sum(recovered_shp_lite
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Another way -
to_date('2012','yyyy') + (coll_date - trunc(coll_date,'yy'))
to_date('2012','yyyy') + (coll_date - trunc(coll_date,'yy'))
>>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;
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'
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. :-(
Well, if you want to get fancy, then you could do this:
add_months(coll_date, (2012 - to_number(to_char(coll_dat e, '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.
add_months(coll_date, (2012 - to_number(to_char(coll_dat
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.
ASKER
thanks
ASKER
TO_DATE(TO_CHAR(COLL_DATE,