Date Calculations
Summary: This technote explains how to do date calculations giving examples of some common applications. You may also want to review technote 4025: All About Dates for a review of how Superbase handles and stores date types.
Date keyword summary
DATE$		Returns a text string from a Julian date number			? "DATE$: ", DATE$ ( TODAY ,"mmmm-dd-yyyy")
DATEBASE	Set the format for date or time values			DATEBASE "mmmm dd yyyy"
DAY		Take a date and return the day of the month as a numeric value			? "DAY : ", DAY ( TODAY ) 
DAY$		Take a date and return the day of the week as a text string 			? "DAY$ : ", DAY$ ( TODAY )
DAYS		Return the data as a Julian date number from a text string or number value			? "DAYS: ", DAYS ( TODAY )
MONTH	Take a date and return the month of the year as a numeral			? "MONTH: ", MONTH ( TODAY )
MONTH$	Take a date and return the month of the year as a text string			? "MONTH$: ", MONTH$ ( TODAY )
TODAY		Return the current system date				? "TODAY: ", TODAY 
YEAR		Take a date and return the year as a number			? "YEAR:  ", YEAR ( TODAY )
Converting a text field to a date field
If your current database has dates stored in text fields, you must convert the fields to a date type before you can do any calculations. The steps to do this are as follows:
Modify the file definition
	Introduce an additional field of data type DATE
	Format the date as desired.
	Add the calculation DAYS(textdatefield.filename)
	Save the file definition

Make sure the date format under Options / Date is the same as the format set for the date field.
Select Update from the Data menu
	Leave the filter blank
Place datefield.filename = datefield.filename in the Update box and click OK. This forces every record to be recalculated.
Delete the original text date field.
Filter for empty date fields
Because a date is stored as a Julian number, you cannot search for blank date fields. Instead the search looks for occurrences where the function DAYS does not return a date, but returns 0. For example the filter:
	SELECT WHERE DAYS(datefield.filename) = 0
finds all records where datefield is blank.
Calculate a person's age given their date-of-birth
To create a file level calculation that returns how old a person is accurate to within one day.
Create a field of date type that contains a person's birthdate
Create a second field of numeric type that contains their age.
Place the following calculation in this second numeric field.
REM If the date of birth is not a leap year, then subtract the date and 365 else subtract the date and 364.
IF ( YEAR (dob.BIRTHDAY) MOD 4 > 0, YEAR ( TODAY - dob.BIRTHDAY - 365), YEAR ( TODAY -dob.BIRTHDAY - 364))
OR If you are not worried about leap year the following is slightly simpler
YEAR ( DAYS ( TODAY ) - DAYS (dob.BIRTHDAY) + 1) - 1
					How long until.....?
'******************************************************************************
 ' * HowLong$() - Returns how many years, months and days from today until a date within +- 2 days.
 ' * The margin of error occurs because all months are averaged to 30 days.
 ' * DueDate.file - name of date field
 ' * 
 ' * The function can be used as follow:
 ' * Example : ? HowLong$(DueDate.file)
 ' *       If DueDate.file contains 09/03/93
 ' * Result  : 0 Yrs., 3 Months and 23 Days
 ' * 
 ' * or the code can be used as a Field Calculation.
 ' * STR$ ( INT ((DueDate.file - TODAY ) / 365)) + " Yrs. " + ( STR$ ( INT (((DueDate.file - TODAY )
 ' * MOD  365) / 30))) + " Mos. " +  STR$ ( INT (((DueDate.file - TODAY ) MOD 365) MOD 30)) + " Days "
 ' ******************************************************************************
FUNCTION HowLong$()
	dys% = DueDate.file - TODAY 
	yrs% = INT (dys% / 365)
	dys% = dys% MOD 365
	mos% = INT (dys% / 30)
	dys% = dys% MOD 30
REM The following 2 lines should be typed as 1.
	HowLong$ = LTRIM$ ( TRIM$ ( STR$ (yrs%))) + " Yrs., " + LTRIM$ (( TRIM$ ( STR$ 
	(mos%)))) + " Months and " + LTRIM$ (( TRIM$ ( STR$ (dys%)))) + " Days "
 END FUNCTION 
What day is it?
'******************************************************************************
 ' * DOW%() - Extracts the Day Of the Week from the given date and returns a number. This is useful 
 ' * when reporting. It allows you to order by day of the week.
 ' * DateField% - Date to be converted
 ' * 
 ' * Example : ? DOW%( TODAY )
 ' * Example : ? DOW%(DateIn.time)
 ' * Example : ? DOW%( DAYS ("1/26/58"))
 ' * Result : 1 if the day is Sunday
 ' * 
 ' * The following line can be used in a Field Calculation or Default
 ' * FIX ((( INSTR ("SuMoTuWeThFrSa", LEFT$ ( DAY$ ( TODAY ),2)) / 2) + .4),0)
 ' * or
 ' * FIX ((( INSTR ("SuMoTuWeThFrSa", LEFT$ ( DAY$ (datefield.file),2)) / 2) + .4),0)
 ' *
 ' * Note: The function requires that DateField% be the same order (MDY) as the System Date Format. 
 '*  This can be changed using the DATEBASE "MM/DD/YY" command or under the Options/Date 
 '*  menu.
 ' *
****************************************************************************
FUNCTION DOW%(DateField%)
    DOW% = FIX ((( INSTR ("SuMoTuWeThFrSa", LEFT$ ( DAY$ (DateField%),2)) / 2) + .4),0)
END FUNCTION 

What Quarter is it? 
******************************************************************************
 ' * Qtr%() - Extracts the Quarter of the Year from the given date
 ' * DateField% - Date to be converted
 ' * 
 ' * Example : ? Qtr%( TODAY )
 ' * Example : ? Qtr%(DateIn.time)
 ' * Result  : 1 if the month is January, February, or March
 ' * 
 ' * The following line can be used in a Field Calculation or Default
 ' * FIX ((( INSTR ("JanFebMarAprMayJunJulAugSepOctNovDec", LEFT$ (( MONTH$ ( TODAY  
 ' *  )),3))) / 9) + .4,0)
 ' * or
 ' * FIX ((( INSTR ("JanFebMarAprMayJunJulAugSepOctNovDec", LEFT$ (( MONTH$ (Datefield)),3))) 
 ' * / 9) + .4,0)
 ' *
 ' * Note: The function requires that DateField% be the same order (MDY) as the System Date Format.    
 ' *This can be changed using the DATEBASE "MM/DD/YY" command or under the Options/Date  
 ' *menu.
****************************************************************************** 
FUNCTION Qtr%(DateField%)
    REM The following statement should be typed as 1 line
    Qtr% = FIX ((( INSTR ("JanFebMarAprMayJunJulAugSepOctNovDec", LEFT$ (( MONTH$   
       (DateField%)),3))) / 9) + .4,0)
 END FUNCTION 

How many working days between two dates?
'******************************************************************************
'* WrkDay() -This program computes the total number of working days between two dates.  The process 
'*adjusts the total time period to a multiple of whole weeks, then divides this multiple by 7 to get the total 
'*number of weeks. The total number of weeks is then multiplied by five to get the total number of weekdays.  
'*It then corrects the total number of weekdays by the correction factor used to produce the multiple of whole 
'*weeks which was stored away in the first step. Further corrections are made to the total number of weekdays 
'*by a fixed correction factor that compensates for starting and ending dates beginning on a Saturday or 
'*Sunday. The results are then printed.
'*
'*bd$		Begin date string entered
'*ed$		End date string entered
'*bdj&%		Begin date - Julian value
'*edj&%		End date - Julian value
'*bdjn&%	Begin Julian date adjusted
'*edj&%		End Julian date adjusted
'*wda%%	Working day correction factor
'*er1$, er2$	Error message strings
'*om1$ -om4$	Output error message strings
'*Note: Holidays could also be added
'******************************************************************************
SUB main()
  CALL WrkDay()
 END SUB 
 
SUB WrkDay()
 CLS 
 
 REM Set the date format as JUL 21,1993.
 DATEBASE "mmm dd,yyyy"
  
 REM Input the dates. 
 ASK "ENTER A BEGINNING DATE:  ";bd$
 ASK "  ENTER AN ENDING DATE:  ";ed$
 
 REM Convert the date strings entered to Julian numbers. 
 bdj&% = DAYS (bd$):edj&% = DAYS (ed$)
 
 REM Error message strings output beginning date entered occurs after ending date entered.
 er1$ = "INCORRECT ENTRY !":er3$ = UCASE$ (ed$) + " !"
 er2$ = "BEGINNING DATE - " + UCASE$ (bd$) + " - EXCEEDS ENDING DATE - "
   
 REM Check for beginning date entered > ending date entered, Print & Exit.
 IF bdj&% > edj&% THEN 
    REQUEST er1$,er2$ + er3$
    END SUB 
 END IF 
 
 REM Adjust beginning date to start on a Saturday, and set the correction factor for the day of week the  
 REM beginning date occurred on.
 
 SELECT CASE DAY$ (bdj&%)
 CASE "Sunday"
    bdjn&% = bdj&% - 1:wda%% = 0
 CASE "Monday"
    bdjn&% = bdj&% - 2:wda%% = 0
 CASE "Tuesday"
    bdjn&% = bdj&% - 3:wda%% = - 1
 CASE "Wednesday"
    bdjn&% = bdj&% - 4:wda%% = - 2
 CASE "Thursday"
    bdjn&% = bdj&% - 5:wda%% = - 3
 CASE "Friday"
    bdjn&% = bdj&% - 6:wda%% = - 4
 CASE ELSE 
    bdjn&% = bdj&%:wda%% = 0
 END CASE 
 
 REM Adjust ending date to end on a Friday, and further modify the correction factor for the day of week 
 REM the ending date occurred on.
  
 SELECT CASE DAY$ (edj&%)
 CASE "Saturday"
    edjn&% = edj&% + 7:wda%% = wda%% - 5
 CASE "Sunday"
    edjn&% = edj&% + 6:wda%% = wda%% - 5
 CASE "Monday"
    edjn&% = edj&% + 5:wda%% = wda%% - 4
 CASE "Tuesday"
    edjn&% = edj&% + 4:wda%% = wda%% - 3
 CASE "Wednesday"
    edjn&% = edj&% + 3:wda%% = wda%% - 2
 CASE "Thursday"
    edjn&% = edj&% + 2:wda%% = wda%% - 1
 CASE ELSE 
    edjn&% = edj&% + 1
 END CASE 
 
 REM All the numbers and corrections are set. Do the math and print
 
 om1$ = "THERE ARE/IS " + LTRIM$ ( STR$ ((edjn&% - bdjn&%) / 7 * 5 + wda%%))
 om2$ = " WORKING DAYS/DAY FROM " + UCASE$ ( DAY$ (bdj&%))
 om3$ = UCASE$ (bd$) + " TO " + UCASE$ ( DAY$ (edj&%)) + " "
 om4$ = UCASE$ (ed$) + " !"
 
 REQUEST om1$ + om2$,om3$ + om4$
 
END SUB 



SBLEXTEN SBP
Superbase is shipped with several user-defined SUB and FUNCTION procedures. If you installed the sample applications, the user-defined procedures are in the SAMPLE\PROCS sub directory of the directory that holds your Superbase program files. Below are some user-defined procedures that are related to dates. To access these routines load SBLEXTEN.SBP. For information on other user-defined procedures refer to chapter 6 of the Developing Applications manual.
AddWorkDay%		Add or subtract a certain number of working days to a date
IsDate$			Determine if a string is equal to a valid date
IsLeapYear%		Returns True if the specified date falls in a leap year
WeekDay%		Return the week day number of a date
MonthYear%		Returns a string containing the month and year from a date
















Program:	Superbase	
Versions:	2.0
Date:	November 9, 1993		
D
Date: