Working date math - me too - 4gl

Since we are all posting our working day math routines. Here's my 4gl one. I pulled this out of our scheduler which has to do working day math. This function adds a number of days to a base time returning the new datetime. It handles holidays - whatever you set them to. Requires: create table holidays ( off_day date ); where off_day is any day which is a holiday ##################################################################### # working day math (add) # basetime is a moment in time (DATETIME Y TO S), # no_days is the number of days to add (INTERVAL DAY TO DAY) ##################################################################### FUNCTION work_day_add(basetime, no_days) DEFINE basetime, newtime DATETIME YEAR TO SECOND, offset, i, j INTEGER, no_days INTERVAL DAY TO DAY, cnvrt CHAR(5), int_days INTEGER LET offset = WEEKDAY(basetime) # what is offset from SUNDAY? CASE offset WHEN 6 LET basetime = basetime + 1 UNITS DAY LET offset = 0 # need to add back in later WHEN 0 LET basetime = basetime OTHERWISE LET basetime = basetime - offset UNITS DAY END CASE LET no_days = no_days + offset UNITS DAY # don't lose those days we # subtracted LET cnvrt = no_days # convert from INTERVAL... LET int_days = cnvrt # to INTEGER LET int_days = int_days/5 # how many weeks? LET int_days = (int_days * 7) + (cnvrt MOD 5) - 1 # add that many + remaindr IF cnvrt MOD 5 < 2 THEN # when TH or FR mess-up - fix LET int_days = int_days - 2 END IF LET newtime = basetime + int_days UNITS DAY # Add those days in # We now have the basic working day, but what about holidays? SELECT COUNT(*) # count how many we spanned INTO offset FROM holidays WHERE off_day BETWEEN basetime AND newtime FOR i = 1 TO offset # for that many, add one_by_one LET newtime = newtime + 1 UNITS DAY LET j = WEEKDAY(newtime) # worry about SAT, SUN is impossible IF j = 6 THEN LET newtime = newtime + 2 UNITS DAY END IF LET j = 1 WHILE j = 1 # we could be hitting another # holiday SELECT COUNT(*) INTO j FROM holidays WHERE off_day = newtime IF j = 0 THEN EXIT WHILE ELSE LET newtime = newtime + 1 UNITS DAY IF WEEKDAY(newtime) = 6 THEN # and watch for saturday LET newtime = newtime + 2 UNITS DAY END IF END IF END WHILE END FOR RETURN newtime END FUNCTION cheers j. _____________________________________________________________________________ Jack Parker - Hewlett Packard, DMD/IS Boise, Idaho, USA jparker@hpbs3645.boi.hp.com _____________________________________________________________________________ "I'm with the IRS, I'm here to help you" _____________________________________________________________________________ Any opinions expressed herein are my own and not those of my employers. _____________________________________________________________________________