Re: confusing date question

Lothar Armbruester wrote:

> Cynic wrote:
> > I have a script that updates dates on a db. However, now I am supposed
> > to
> > take into account the day of the week. So, for example, if the date is
> > July
> > 7, 1999 (Wednesday) I need to update to July 7, 2001. HOWEVER: if July
> > 7,
> > 2001 is NOT a thursday, but a SATURDAY, I need to make it a
> > non-weekend work
> > day. So here is what I need to do:
> >
> > July 7, 1999 ---->>> July 9, 2001 (this is the first work day
> > following
> > July 7, 2001)
> > So, I guess I am adding 2 years here PLUS whatever it will take to get
> > the
> > day to the work week.
> > Any suggestions?
> >
> > Here is what I have so far (doesn't take into consideration the whole
> > weekend thing):
> >
> > UPDATE GL_ACCT_SCHED SET EFF_DT = ADD_MONTHS(EFF_DT,
> > (TRUNC((MONTHS_BETWEEN( TO_DATE('31-DEC-2000', 'DD-MON-YYYY'),
> > EFF_DT))/12,0)*12)+12) where MONTHS_BETWEEN(TO_DATE('01-JAN-2001',
> > 'DD-MON-YYYY'), EFF_DT) > 0;
> >
> Hello Cynic, try this:
>
> update gl_acct_sched set
> eff_dt = add_months(eff_dt,24)+
> decode(to_char(add_months(eff_dt,24),'D'),'6',2,'7',1,0)
> where
> eff_dt<to_date('2001-01-01','YYYY-MM-DD');
>
> If I understood your problem correctly, you want to add two years to
> your dates plus a small offset bringing them to the next workday.
> add_months(eff_dt,24) adds the two years.
> to_char(add_months(eff_dt,24),'D') gives the day of week '1' being
> monday. So the decode adds two days for saturday, one day for sunday and
> nothing for all other days.
>
> Hope that helps,
> Lothar
>
> --
> Lothar Armbrüster | la_at_oktagramm.de
> Hauptstr. 26 | la_at_heptagramm.de
> D-65346 Eltville | lothar.armbruester_at_t-online.de

Nicely done.

Daniel Morgan Received on Mon Jul 22 2002 - 12:59:38 CDT