Date and Time

[Rick Kelly]

Date and Time

Here is my first cut at a general purpose way of adding business days to a base date.

To help you understand the code a bit better...

The dates are 32 bit integers representing the number of days since Jan 1, 1 which is a Monday.

I use Sun=0....Sat=6 as my values for the 7 weekdays defined as %SUNDAY - %SATURDAY.

The function cmGregorianWeekday is just MOD 7 operation that also handles negative numbers so that the result is always in the range 0-6.

cmWeekDayAfter just calculates the next requested day of the week.

cmWeekDayBefore just calculates the first prior day of the week.

 
FUNCTION cmGregorianBusinessAddWeekdays (BYVAL dwStart AS LONG, _
                                         BYVAL dwEnd AS LONG, _
                                         BYVAL dwSign AS LONG, _
                                         BYVAL dwSunday AS LONG, _
                                         BYVAL dwMonday AS LONG, _
                                         BYVAL dwTuesday AS LONG, _
                                         BYVAL dwWednesday AS LONG, _
                                         BYVAL dwThursday AS LONG, _
                                         BYVAL dwFriday AS LONG, _
                                         BYVAL dwSaturday AS LONG) AS LONG
'
' Add/Subtract days range to adjust for days of the week that are non business days
'
' dwSunday through dwSaturday, %TRUE=business day, %FALSE=non business day
'
' Returns fixed date representing the range extension either backwards from dsStart
' or forwards from dwEnd
'
' dwSign = 1 if range extension is forward from dwEnd, -1 if from dwStart
'
DIM dwNonBusinessDays       AS LOCAL LONG
DIM dwLoop                  AS LOCAL LONG
   dwNonBusinessDays = -1
    WHILE dwNonBusinessDays <> 0
        dwNonBusinessDays = 0
        IF dwSunday = %FALSE THEN
            dwNonBusinessDays = cmGregorianWeekdayCount(%SUNDAY,dwStart,dwEnd)
        END IF
         IF dwMonday = %FALSE THEN
            dwNonBusinessDays = cmGregorianWeekdayCount(%MONDAY,dwStart,dwEnd) _
                              + dwNonBusinessDays
        END IF
        IF dwTuesday = %FALSE THEN
            dwNonBusinessDays = cmGregorianWeekdayCount(%TUESDAY,dwStart,dwEnd) _
                              + dwNonBusinessDays
        END IF
        IF dwWednesday = %FALSE THEN
            dwNonBusinessDays = cmGregorianWeekdayCount(%WEDNESDAY,dwStart,dwEnd) _
                              + dwNonBusinessDays
        END IF
       IF dwThursday = %FALSE THEN
            dwNonBusinessDays = cmGregorianWeekdayCount(%THURSDAY,dwStart,dwEnd) _
                              + dwNonBusinessDays
        END IF
        IF dwFriday = %FALSE THEN
            dwNonBusinessDays = cmGregorianWeekdayCount(%FRIDAY,dwStart,dwEnd) _
                              + dwNonBusinessDays
        END IF
        IF dwSaturday = %FALSE THEN
            dwNonBusinessDays = cmGregorianWeekdayCount(%SATURDAY,dwStart,dwEnd) _
                              + dwNonBusinessDays
        END IF
' If there are some non business weekdays found, adjust the range for next pass
        SELECT CASE dwNonBusinessDays
        CASE > 0
' Test for which end of the range to adjust, make the adjustment and reset the new range
            SELECT CASE dwSign
            CASE = 1
               SELECT CASE dwEnd - dwStart + 1
                    
                CASE <> dwNonBusinessDays           ' If all the days in the range are non-business
                    dwStart = dwEnd + dwNonBusinessDays
                    dwEnd = dwEnd + 1
                    
                CASE ELSE
                    
                    dwStart = dwEnd + 1             ' Loop through one day at a time until a business day is found
                    dwEnd = dwStart
                    
                END SELECT
            CASE ELSE
               SELECT CASE dwEnd - dwStart + 1
                    
                CASE <> dwNonBusinessDays
                    dwEnd = dwStart - dwNonBusinessDays
                    dwStart = dwStart - 1
                    
                CASE ELSE
                    dwEnd = dwStart - 1             ' Loop through one day at a time until a business day is found
                    dwStart = dwEnd
                    
                END SELECT
            END SELECT
' Reverse to reset range
            SWAP dwStart, dwEnd
        END SELECT
    WEND
    FUNCTION = IIF(dwSign = 1,dwEnd,dwStart)        ' Check which end of the range to return
END FUNCTION
FUNCTION cmGregorianWeekdayCount (BYVAL dwWeekday AS LONG, _
                                  BYVAL dwStart AS LONG, _
                                  BYVAL dwEnd AS LONG) AS LONG
' Calculate the number of ocurrences of dwWeekDay (%SUNDAY - %SATURDAY) in the
' date range provided       
DIM dwCount                 AS LOCAL LONG
' Start date must be less or equal to ending date
    SELECT CASE dwStart
    CASE > dwEnd
        SWAP dwStart, dwEnd
    END SELECT
' If Start date and ending date are equal, return 0 days
    SELECT CASE dwStart
    CASE = dwEnd
        dwCount = 0
    CASE ELSE
        dwCount = INT(ABS(cmWeekdayAfter(dwWeekday,dwEnd) _
               - cmWeekdayBefore(dwWeekday,dwStart)) / 7) _
               - 1
    END SELECT
    FUNCTION = dwCount
END FUNCTION

A quick example:

Add 20 business days to April 1, 2009, Sat/Sun are non business weekdays

April 1, 2009 = 733498 + 20 = 733518 or April 21, 2009

Calling the function as:

cmGregorianBusinessAddWeekdays (733498, _
733518,
1, _
0, _ ' Sunday
1, _
1, _
1, _
1, _
1, _
0) ' Saturday

The first pass through the WHILE/WEND loop in cmGregorianBusinessAddWeekdays finds 3 Sundays and 3 Saturdays for a total of 6 non business days. The inspected date range is adjusted to 733519 (April 22, 2009) through 733524 (April 27, 2009).

The second pass through the WHILE/WEND loop finds 1 Sunday and 1 Saturday for a total of 2 non business days for the April 22-27, 2009 range. The inspected date range is adjusted to 733525 (April 28, 2009) through 733526 (April 29, 2009).

The thrid pass through the WHILE/WEND loops finds 0 Sunday and 0 Saturday for a total of 0 (zero). We are done and the answer is April 29, 2009.

Before this function is called, the range has to adjusted to be sure the lower part of the range is passed first and that at least one weekday is specified as a normal business day (or a loop race will result). One special case had to be handled in cmGregorianBusinessAddWeekdays to handle the situation where the entire initial range is composed of non-business days. In that case, we just revert to looping one day at time to find the next business work day.

I'm out of state for the coming week. Enjoy!

Rick

[John Petty]

I doubt they will. For your reference the API's use a similar format of a double with the interger portion being a day count and the fractional portion being the fraction of a day. The difference between them is Filetime represents the number of 100-nanosecond intervals since January 1, 1601 (UTC). VariantTime is not tied to a particulat time zone and starts with 1 as 31 December 1899 and does not report milliseconds.

[Rick Kelly]

Date and Time

Thanks to all for the feedback. It just occurred to me that perhaps I'm viewing things a bit differently since I'm putting together a generic set of routines where I don't know in advance what may be coming at me.

It seems that this is a code friendly group so I'll put some of code snippets up as things come along.

One of our most obvious differences is that internally, my DLL uses Rata Die dates which is just a 32-bit integer representing the number of days since Jan 1, 1, my TIME type is a 32 bit integer representing the number of milliseconds since midnight and my DATETIME is a 64-bit integer representing the number of milliseconds since Jan 1, 1. Internally, I also use 64-bit floating point numbers where the fractional portion represents a portion of a day and I've borrowed and call them MOMENTS. Although I expose functions in my DLL that deal with converting to and from these types, all normal functions expose date/time using separate DWORD's for month, day, year, hour, minute, second and millisecond, weekday, etc.

Although non-sensical at times, I can handle dates from the year -9999 to +9999 comfortably without using Win32 api calls. I'm not sure if I will also port over all the code that deals with the many other calendars such as Hebrew, Chinese, Islamic, etc. I do have the minimum 3 (Gregorian, Julian (Old Style) and ISO) I most frequently use already in place as well as Christian/Othodox easter calculations and I'll stick in all the routines that handle timezones (I have a rules matrix for 19 different ones) and to/from/calculate UTC vs Local vs Standard vs Apparent time, etc.

It has been my practice to store only UTC date/times in my databases for locale sensitive columns and convert them to local time for reports/display and sometimes I have to mix several different locales on the same screen/report. Thinking about all this and now I'm a bit fatigued.

As a PB newbie, no giggles please when you see some of my code.

[John Petty]

Also if you wish to maintain compatability with other major versions of basic you can also use SystemTimeToVariantTime and its logical reverse VariantTimeToSystemTime. Surprisingly it doesn't use any variants.

[Michael Mattias]

This is what I use to add some number of days to a date. My input is a date, my output must be the next workday (M-F) on or after the specified number of days (which may be postive or negative) is added to that date.

' --------------------------------------------------------------------
' INTERNAL FUNCTION TO COMPUTE NEW DATE FROM INPUT DATE and NUMBER OF DAYS
' --------------------------------------------------------------------
' return input date + nDays (which may be signed) as a CCYYMMDD string
' Working OK 4-24-04. always returns a day mon-fri
FUNCTION f_AddWorkDays (CCYYMMDD AS STRING, nDays AS LONG) AS STRING

    LOCAL st AS SYSTEMTIME
    LOCAL ft AS ftunion
    LOCAL nDaysDone AS LONG, E AS LONG

    IF LEN(CCYYMMDD) <> 8 OR ISTRUE(VERIFY(CCYYMMDD,"0123456789")) THEN
        FUNCTION = ""
        EXIT FUNCTION
    END IF

    RESET           st
    st.wyear     =  VAL(MID$(CCYYMMDD, 1,4))
    st.wmonth    =  VAL(MID$(CCYYMMDD, 5,2))
    st.wDay      =  VAL(MID$(CCYYMMDD, 7,2))

    ' convert starting date to filetime and validate date
    E       = SystemTimeToFileTime (st, ft.ft)
    IF ISFALSE E THEN
    '    E = GetLastError
      '  MSGBOX "ST2FT Failed on Error " & STR$(E)
        ' fails on error zero if invalid date
        FUNCTION = ""  ' could be "INVALID" or "BADDATE" or something just as easily
        EXIT FUNCTION
    END IF


    nDaysDone =  0&

    DO UNTIL nDaysDone = nDays
        ' add or subtract a day depending on direction
        IF nDays < 0 THEN
            ft.q  = ft.q - %ONE_FT_DAY
        ELSE
            ft.q  = ft.Q + %ONE_FT_DAY
        END IF
        ' check DOW. If not sat or sun, incr or decr daysdone
         FileTimeToSystemTime  ft.ft, st
         SELECT CASE st.wDayofWeek
            CASE 0??,  6??    ' Sun or Sat
                ' do nothing
            CASE ELSE
                IF nDays > 0 THEN
                   INCR nDaysDone
                ELSE
                   DECR nDaysDone
                END IF
        END SELECT
    LOOP

    ' when we get here, SystemTime contains the ending date, so convert to CCYYMMDD

    FUNCTION = FORMAT$(st.wYear, "0000") & FORMAT$(st.wMonth, "00") & FORMAT$(st.wDay, "00")


END FUNCTION

All you have to do to account for holidays is in here.

            CASE ELSE     '  [B]Its not sat or sun [/B]
                **************************************
                IF "is a Holiday "  treat it as a Sat or Sun and 
                do nothing else ; that is, allow the program 
                to test the next day 
               ' ***********************************
                IF nDays > 0 THEN
                   INCR nDaysDone
                ELSE
                   DECR nDaysDone
                END IF

I have found that fast is relative until you get into larger ranges of time and I want this function to be fast under all conditions

This is plenty fast, and it's not even optimized.. however, I really could not optimize this if I had to check for holidays.

But in your design, opting to store holiday rules instead of holiday dates, you could just build a table of all holidays in the range mindate to maxdate once, and scan it to see if you have a hit. Should be plenty quick.

MCM

[Rick Kelly]

Date and Time

I thought an example of the efficiencies that are available in the algorithmic approach to calculating a business date might be helpful.

Assuming that we are starting with April 1, 2009, what is the date 20 business days from now?

Lets also assume that Saturday/Sunday are the weekdays that are non-business days.

1. April 1 + 20 days = April 21

2. Saturday before April 1 = 3/28 and Saturday after 4/21 = 4/25

3. Number Saturdays between 3/28 and 4/25, not counting 3/28 is 4

4. Sunday before April 1 is 3/29 and Sunday after 4/21 is 4/26

5. Number Sundays between 3/29 and 4/26, not counting 3/29 is 4

6. Final result is April 21 + 4 Saturdays + 4 Sundays = 4/29

It doesn't matter how many business days you want to use, the steps are the same and the algorithmic approach performance remains constant. It's possible under certain circumstances to add one additional direct calculation if the final result falls on a non-business day of the week.

Cheers!

Rick

[Rick Kelly]

Date and Time

Thank you for the feedback Michael...responses below

[Michael Mattias;314298]Easy, fast. Also the way I do it. probably because it's easy and fast.

I have to do exactly the same thing, add some number of days to a date not including weekends. (I don't have to do holidays, but that's the way I set up the code if I ever had to do so.

I have found that fast is relative until you get into larger ranges of time and I want this function to be fast under all conditions without concern to the expanse of time. I have a function that can tell me the number of occurences of a weekday for any period of days this is always very fast.

Holiday Rules?

You just enter in a database or file

20091225     ; chistmas
20090914     ; arbor day
....

You can load these into an array, then just ARRAY SCAN the current date for a hit... if you get a hit, it's a holiday.

"assuming" you are doing all this using FILETIME and SYSTEMTIME structures, you can just use GetDateFormat() against current date being tested (if not a weekend) and Windows will format the string for you.

MCM

I am not using FILETIME/SYSTEMTIME structures. It is a custom structure that contains holiday rules necessary to calculate the date for the applicable year. I am striving to avoid having my applications store any holiday dates opting for storing the rules for their calculation instead.

Rick

[Michael Mattias]

1. Loop through, adding one day at a time, and checking each day against the weekday and holiday rules until the requisite number of days have been added

Easy, fast. Also the way I do it. probably because it's easy and fast.

I have to do exactly the same thing, add some number of days to a date not including weekends. (I don't have to do holidays, but that's the way I set up the code if I ever had to do so.

Holiday Rules?

You just enter in a database or file

20091225     ; chistmas
20090914     ; arbor day
....

You can load these into an array, then just ARRAY SCAN the current date for a hit... if you get a hit, it's a holiday.

"assuming" you are doing all this using FILETIME and SYSTEMTIME structures, you can just use GetDateFormat() against current date being tested (if not a weekend) and Windows will format the string for you.

MCM

[Rick Kelly]

First time Powerbasic user here and before I begin my next development cycle I want to port and consolidate any number of common routines I have grown accustomed to into one DLL.

This being my maiden venture, so to speak, and hopefully I will learn and grow familiar with this language.

So the creation of this DLL begins...and the first area of interest covers date and time functions. I have in the past developed a complete port (in assembler) cover to cover the algorithms presented in the books Astronomical Algorithms, Second Edition by Jean Meeus and Calendrical Calculations: The Millennium Edition by Edward M. Reingold and Nachum Dershowitz along with a myriad of other custom functions I have found useful. So far, the port is going smoothly and PB is certainly proving an easy master to work for although a ways to go to approach my enjoyment of assembler (MASM32).

With introductions to the group over with, this upcoming project encapsulates a type of business and I'm going to be in need of some business based date calculations that inevitably involve designation of non-business days both as recurring (week days that are workdays/non-workdays) during a year and special days that typically do not reoccur during the year usually spoken of as holidays.

I am not a fan of storing dates (which require someone to maintain) and have taken a break from my DLL port to add a rules-based function that I really like that is capable of calculating every gregorian based public holiday I have been able to find on the internet when provided the gregorian year and the rules describing the holiday.

Given a base date and some number of days to add/subtract which is very simple to do, if you add which days of the week are business or non-business and a list of the observed holidays (as a group of rule sets) two approaches come to mind for calculating the answer.

1. Loop through, adding one day at a time, and checking each day against the weekday and holiday rules until the requisite number of days have been added

2. Calculate the result directly via an algorithmic approach

In keeping with my own and the PB battle cry of efficiency, I have delved into approach 2. It boils down to this:

1. Calculate the preliminary ending date by just adding the presented days.

2. Extend the ending date by the number of non-business weekdays found in the range calculation in step 1

3. Repeat step 2 until the new ending date is a normal business workday.

4. Examine each holiday and check if it is included in the current range and bump the ending date up when true only if the holiday also falls on a weekday that is workday

5. If step 4 is true, redo the weekday check and restart step 4

6. Done when step 4 finds no applicable holidays

There exists the possibility of an endless loop race that I think is avoided as long as one day of the week is a normal workday and can hope that nobody would present 52 or 53 holidays that all fall on that same day of the week and cover an entire gregorian year.

Of course, I have to keep track of when I extend the range past December 31st and regenerate holidays as needed which dictates that the holidays presented to the function be rules based.

In my long winded way, are there any forum members with knowledge or tips on a better way to do this calculation? If I can this to work, I will never, never again store holiday dates, I promise!

Another goal of mine is that this PB DLL be language neutral. If I am to use a set of holiday rules that contains a variable number of rule groups, is it possible, from PB to call this function and pass an array that the DLL just sees as one contiguous segment of memory? I haven't read up yet on how to take this pointer in my PB DLL and use it to parse the rule groups given the variable nature of the the entire structure size.

TIA

Rick Kelly