Date calculation stuff.

This note covers both of the questions asked yesterday. It includes 3 4gl functions along with wrappers. work_days: which computes the number of working days between two dates work_day_add: when given a date and a number of days (n), returns the (working) date which is 'n' days beyond the original date last_day: Returns the last day of a month. I was rather upset to discover that my date routine library seems to have vanished, and had to scramble around to dig these three up. I believe this answers the first question (working day arithmetic) although it's in 4gl, but that should convert rather easily to SPL. As for the month arithmetic - I wasn't thinking properly yesterday - as Dave (?) pointed out, unless you can define how long the month in question is how can you do month arithmetic. Last_day will help you there, it returns an integer of the number of days in a given month, you would then have to build this into a loop to subtract each preceeding month. I originally posted the last_day function some time back, and Bob Baskett and I went around a few times on it - hence the highlights for a couple of the datatypes. hope these help. It would be nice to see the converted SPL code wing its way back to this group - hint, hint. cheers j. MAIN DEFINE s_date, e_date DATETIME YEAR TO DAY, w_days INTERVAL DAY(3) TO DAY PROMPT "Enter start date" for s_date # e.g. 1995-09-02 PROMPT "Enter end date" for e_date CALL work_days(s_date, e_date) RETURNING w_days DISPLAY "Work days: ", w_days END MAIN ##################################################################### # Non-inclusive number of work days between two dates # so between monday and tuesday would be 1 ##################################################################### FUNCTION work_days(s_date, e_date) DEFINE s_date, e_date DATETIME YEAR TO DAY, offset INTEGER, offitv INTERVAL DAY(3) TO DAY, wk_days INTERVAL DAY(3) TO DAY, adjust INTERVAL DAY TO DAY # # Problem: when start date is a sunday or saturday things get hokey, since # these AREN'T work days, lets just avoid the problem by skipping them # # Fix start date LET offset = WEEKDAY(s_date) # What IS today? IF offset = 0 THEN LET s_date = s_date + 1 UNITS DAY END IF IF offset = 6 THEN LET s_date = s_date + 2 UNITS DAY END IF # Fix end date LET offset = WEEKDAY(e_date) # What IS today? IF offset = 0 THEN LET e_date = e_date + 1 UNITS DAY END IF IF offset = 6 THEN LET e_date = e_date + 2 UNITS DAY END IF LET offset = WEEKDAY(s_date) - 1 # step back to monday LET offitv = offset USING "-##&" # convert to interval LET s_date = s_date - offitv # we are now on monday LET wk_days = e_date - s_date # number of intervening days LET adjust = (wk_days/7)*2 # number of weeks (weekends) LET wk_days = wk_days - adjust - offitv RETURN wk_days END FUNCTION # 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, no_days is the number of days to add. ##################################################################### 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 database foo main define t_date date define p_date date define l_day integer # ^^^^^^^^^^^^^^^^^^^^^ # other stuff. # --- define yr integer # to get year define mnth integer # to get month define strg char(10) # intermediate # --- let t_date = '01/31/1994' let l_day = last_day(t_date) # ^^^^^ display 'The last day of the month containing ',t_date,' is ',l_day # to get the last DATE of that month do: # --- start of addition let yr=YEAR(t_date) let mnth=MONTH(t_date) let strg= mnth using "##", "/", l_day USING "##", "/", yr USING "####" let p_date = DATE(strg) display 'The last date of the month containing ',t_date,' is ',p_date # --- end let t_date = '01/15/1994' let l_day = last_day(t_date) # ^^^^^ display 'The last day of the month containing ',t_date,' is ',l_day end main # Get the last day of the month. FUNCTION last_day(_current) define _current date DEFINE ld DATE, dn INTEGER # what day number is today? LET dn = DAY(_current) # subtract that many days yields last day last month. # add 1 day gives first day this month # add 1 month gives first day next month # subtract 1 day gives last day this month. # this is different from last_day last month + 1 month --> 30 vs 31. LET ld = (_current - dn UNITS DAY + 1 UNITS DAY ) + 1 UNITS MONTH - 1 UNITS DAY # ^^^^^^^^ # get