Re: decode of date..subtracting weeks

postbus_at_sybrandb.demon.nl (Sybrand Bakker) wrote in message news:<a20d28ee.0209192339.14a136fd_at_posting.google.com>...
> 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')
> from dual;
>
> 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

Thanks!! I didn't know you could use subtractions in add_months. Dumb on me for not trying though....this helps alot and thanks again... DM Received on Sat Sep 21 2002 - 18:47:23 CEST