Re: decode of date..subtracting weeks

DiggidyMack69_at_hotmail.com (DiggidyMack69) wrote in message news:<c86ce4f.0209190721.4022c5cd_at_posting.google.com>...
> Hey folks,
> I am looking for the best way to get from one date say '08/31/2002'
> the first day of the week 51 weeks beforehand (hence a ful year). I'm
> trying to get it in a single decode line. I'm pretty sure the value
> should come up as '08/26/2001'.
>
> '08/31/2002' is the last day of week 35 in 2002.
> I know how to walk back in days like so...but what about weeks then
> days.
>
> SQL> select
> 2 to_char((to_date('08/31/2002','MM/DD/YYYY')-365),'MM/DD/YYYY')
> 3 from dual;
>
> TO_CHAR((T
> ----------
> 08/31/2001

>
> ideas anyone???
>
> thanks in advance for your help,
> DM
> diggidymack69_at_hotmail.com

simple solution (why use decode, if appropiate date functions are available?)

select to_char(next_day(add_months(to_date('31-08-2002','dd-mm-yyyy'

                                   ),-12
                       ),'SUN'
               )-7,'dd-mm-yyyy') 

This subtracts 12 months, compensating for leap years Then it calculates the next sunday
and subtracts 7 days from that.
That is all you need.

Regards

Sybrand Bakker
Senior Oracle DBA Received on Fri Sep 20 2002 - 09:39:46 CEST