Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: confusing date question
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.deReceived on Mon Jul 22 2002 - 11:22:45 CDT