Re: Date calculation functions

> > I am interested in date calculation functions in sql/4gl that will > allow me to figure out weekends, leap year, etc. Please let me > know if you have experience with this or know of any tools that I > can get to do this. > > Thanks in advance! > I am enclosing a 4gl library of such functions. Many of these were garnered from the archives, I added a few. Some are just pseudo code and won't work at all yet - but the library does compile and can be linked in. I ask only that if you complete one of the pseudo-coded functions you send me the code for it so I can update the library. (Yet more stuff on my list of things to do: - integrate functions better (toss duplicates) - finish the missing ones - document same - do up example functions ) cheers j. ________________________________________________________________________ Jack Parker - Hewlett Packard, DMD/IS Boise, Idaho, USA jparker@boi.hp.com Currently on loan to PLD/PE ________________________________________________________________________ Outside of a dog a book is a man's best friend. Inside of a dog it's too dark to read. (Groucho Marx) ________________________________________________________________________ Any opinions expressed herein are my own and not those of my employers. ________________________________________________________________________ ########################################################################### # # datelib.4gl: a standalone library of 4gl date routines. Suitable for # linking. Various functions have been changed so that all # work off of a passed in date. The exception are the string # conversion routines. # # Compiled: JParker # # Various functions written by: # Jonathan Leffler # Bob Baskett # Cathy Kipp # # All functions return NULL(s) if they can't handle what is passed in. # # init_strgs() - all of the strings used in this code, they are split out # so that they will be easy to find and fix for our friends # who do not use English as their primary language. This # routine is called first by any routine which requires them # and finds them unitialized. # #-------- # # dayname(date,num) RETURNS CHAR(9) # returns the dayname of a given date or number. # note - the Informix DAY() routine returns a 0 for sunday # this function supports that concept so: # dayname("",DAY(TODAY)) = dayname(TODAY,"") # # last_day_month(date) RETURNS smallint, date # returns the day number (28,30,31) and date of the last day # of the month for a given date. # # monthname(date,num) RETURNS CHAR(9) # returns the monthname of a given date or number. # # File_Date_Stamp # File_Time_Stamp ########################################################################### GLOBALS DEFINE monthname ARRAY[12] OF CHAR(9), dayname ARRAY[7] OF CHAR(9), w_yest, w_tomor, w_day, w_in, w_ago CHAR(20), quart ARRAY[4] OF DATETIME MONTH TO DAY, decimal_point, hr_delim, dy_delim CHAR(1), DBDATE CHAR(5), hols ARRAY[45] OF RECORD holname CHAR(20), holdate DATE END RECORD, epoch DATE END GLOBALS ##################################################################### FUNCTION init_strgs() # A collection of strings we may/will need IF LENGTH(monthname[1])>0 THEN RETURN END IF LET monthname[1] = "January" LET monthname[2] = "February" LET monthname[3] = "March" LET monthname[4] = "April" LET monthname[5] = "May" LET monthname[6] = "June" LET monthname[7] = "July" LET monthname[8] = "August" LET monthname[9] = "September" LET monthname[10] = "October" LET monthname[11] = "November" LET monthname[12] = "December" LET dayname[1] = "Sunday" LET dayname[2] = "Monday" LET dayname[3] = "Tuesday" LET dayname[4] = "Wednesday" LET dayname[5] = "Thursday" LET dayname[6] = "Friday" LET dayname[7] = "Saturday" LET w_yest = "yesterday" LET w_tomor = "tomorrow" LET w_day = "day" LET w_in = "in" LET w_ago = "ago" # First day of quarter - they vary from company to company. LET q1 = DATETIME(1/1) MONTH TO DAY LET q2 = DATETIME(4/1) MONTH TO DAY LET q3 = DATETIME(7/1) MONTH TO DAY LET q4 = DATETIME(10/1) MONTH TO DAY LET hr_delim = ":" # time delimiter LET decimal_point = "." # delimits fraction LET DBDATE = fgl_getenv("DBDATE") LET dy_delim = DBDATE[5,5] CALL calc_hols() # fill holiday array LET epoch = DATE("1/1/1900") END FUNCTION ##################################################################### FUNCTION cnv_strg_dtm(string) #parse date for month/day/year using delimiter, use DBDATE for probably format #Look for [AaPp][Mm], + 12 hrs if PM and < 11:59, discard #Check for alpha not in am/pm # compare against list and do action. #Month/day/year swap depending on values. # #Keywords: # Tomorrow # Yesterday # IN n days/months # n days/month AGO # monthnames # daynames #RETURN DATETIME (parser) END FUNCTION ##################################################################### FUNCTION cnv_strg_intvl(string ) # (probably the same routine with a switch) #RETURN INTERVAL (parser) END FUNCTION ##################################################################### FUNCTION wk_endpoints(bdate) DEFINE bdate, sdate, edate DATE, i SMALLINT LET i = WEEKDAY(bdate) # day of the week 0-6 LET sdate = bdate - i UNITS DAY LET edate = bdate + (7-i) UNITS DAY RETURN sdate, edate END FUNCTION ##################################################################### FUNCTION Add_Month (bdate, num_months) DEFINE bdate, edate DATE, num_months SMALLINT LET edate = bdate + num_months UNITS MONTH RETURN edate END FUNCTION ##################################################################### FUNCTION Subtract_Month (date, num_months) DEFINE bdate, edate DATE, num_months SMALLINT LET edate = bdate - num_months UNITS MONTH RETURN edate END FUNCTION ##################################################################### FUNCTION date_validate(string) END FUNCTION ##################################################################### FUNCTION greg_to_jul(date) END FUNCTION ##################################################################### FUNC