Re: week of year function
This is a multi-part message in MIME format. --------------3E1036121ACA Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Steffen Kuebler wrote: > > Hi there, > > I'm searching for a date function which returns me the week of the year. > > Can somebody help me ! Attached is a 4gl module that has two versions, depending on what you mean by "week of the year" (Does the week start on a given day of the week like Sunday (so the first week can be shorter than 7 days), or does the week start on the first weekday of the year (e.g. Thursday for 1998)?) This could translate into SPL or NewEra quite readily (I think all base functions like MDY that are used are in both syntaxes), and there's a quite dense single line approach commented in the examples so that you could include it in in-line SQL as well. I believe this was posted in the appropriate FAQ sections some time ago. -- //////////////// ======================================================= ////////// // Dennis J. Pimple Informix Software, Inc. ////// / /// Principal Consultant 6300 S Syracuse Way Ste 205 ///// // //// dennisp@informix.com Englewood CO 80111 //// // ///// /// // ////// recept: 303-850-0210 // // /////// direct: 303-740-5611 Opinions expressed are mine, / /////////// fax: 303-779-4025 and do not necessarily //////////////// http://www.informix.com reflect those of my employer --------------3E1036121ACA Content-Type: text/plain; charset=us-ascii; name="week.4gl" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="week.4gl" ########### # INFORMIX PROFESSIONAL SERVICES ####### # ## Denver, Colorado ###### # ### ================================================== ##### # #### File: %M% SCCS: %I% %P% #### # ##### Program: Week of the year calculator ### # ###### Client: Demo ## # ####### Author: Dennis J. Pimple # ########### Date: %G% %U% # Two ways to determine which week of the year a given date falls # The MAIN is for testing purposes only MAIN DEFINE ldate DATE DEFINE wkct SMALLINT DEFINE cwk CHAR(2) DEFINE txt CHAR(60) OPTIONS PROMPT LINE 15 CLEAR SCREEN WHILE TRUE LET txt = "Enter date, RET for TODAY, or INTERRUPT to quit:" PROMPT txt CLIPPED FOR ldate IF ldate IS NULL THEN LET ldate = TODAY END IF LET wkct = week_number(ldate) LET cwk = wkct USING "#&" LET txt = ldate USING "mm/dd/yyyy", " is in the ", cwk CASE WHEN cwk[2] = "1" LET txt = txt CLIPPED, "st" WHEN cwk[2] = "2" LET txt = txt CLIPPED, "nd" WHEN cwk[2] = "3" LET txt = txt CLIPPED, "rd" OTHERWISE LET txt = txt CLIPPED, "th" END CASE LET txt = txt CLIPPED, " week of the year" DISPLAY txt CLIPPED, "" AT 9,1 END WHILE END MAIN #---------------------------------------------------------------------# FUNCTION week_number(ldate) # Arguments: Date in Question # Purpose: Determine the week of the year for the given date # this version assumes that a "week" begins on the weekday # of 1/1/yyyy; so if the year begins on Wednesday, week # 1 is Wednesday-Tuesday 1/1 through 1/8, etc. # Returns: SMALLINT 1-53 #---------------------------------------------------------------------# DEFINE ldate DATE DEFINE tdate DATE DEFINE wkct SMALLINT # get the first day of the year LET tdate = MDY(1,1,YEAR(ldate)) LET wkct = 0 # loop until we go past our test date WHILE ldate >= tdate LET wkct = wkct + 1 LET tdate = tdate + 7 END WHILE # the above can also be expressed as: #LET wkct = ((ldate - MDY(1,1,YEAR(ldate))) / 7) + 1 # which is faster (in SPLs, one tester claims 15x the speed, # my own 4GL tests showed no significant difference at the 1/100th # second range. I choose the loop for clarity, but you c hacks # will *love* the density of the calculation. # see below for even more! RETURN wkct END FUNCTION # week_number(ldate) { #---------------------------------------------------------------------# FUNCTION week_number(ldate) # Arguments: Date in Question # Purpose: Determine the week of the year for the given date # this version assumes that a "week" ends on Saturday, # so if 1/1 begins on some day other than Sunday, # week 1 is that day through the 1st Saturday, # so it may be as short as 1 day. # Returns: SMALLINT 1-53 #---------------------------------------------------------------------# DEFINE ldate DATE DEFINE tdate DATE DEFINE wkct SMALLINT # get the 1st day of the year LET tdate = MDY(1,1,YEAR(ldate)) # walk up to the 1st Saturday WHILE WEEKDAY(tdate) != 6 LET tdate = tdate + 1 END WHILE LET wkct = 1 # loop until we go past our test date WHILE ldate >= tdate LET wkct = wkct + 1 LET tdate = tdate + 7 END WHILE # all of which could be: # LET wkct = ((ldate - MDY(1,DAY(MDY(1,1,YEAR(ldate)) + (6 - WEEKDAY(MDY(1,1,YEAR(ldate))))), YEAR(ldate))) / 7) + 2 # do I win a prize or something? RETURN wkct END FUNCTION # week_number(ldate) } --------------3E1036121ACA--