Re: week of year function

At 10:36 PM 1/21/98 +0000, David Williams wrote: }In article <34C61A5C.4674@informix.com>, Dennis Pimple }<dennisp@informix.com> writes }>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. }> } } I assume you mean Section 5. Common SQL Questions:? } } I cannot find it, another one to add...Thanks!!! } }-- }David Williams } }Maintainer of the Informix FAQ } Primary site (Beta Version) http://www.smooth1.demon.co.uk } Official site http://www.iiug.org/techinfo/faq/faq_top.html } }I see you standin', Standin' on your own, It's such a lonely place for you, For }you to be If you need a shoulder, Or if you need a friend, I'll be here }standing, Until the bitter end... }So don't chastise me Or think I, I mean you harm... }All I ever wanted Was for you To know that I care } Here is a some standalone SQL that should do the trick: select ( extend(any_date,year to day) - ( mdy(12,31 - weekday(mdy(1,1,year(any_date))), year(any_date)-1) + interval(1) day to day )) / 7 + interval(1) day to day from your_table; Which basically says: Find the day of the year for the given date, adjust for the day of week for Jan 1 of its year, divide by 7 (truncating result) to get number of full weeks prior to date, and then add 1 to get to the one you want. I don't know what the "quite dense single line approach" referred to above actually looks like, but I'm sure this qualifies... - Dave __________________________________________________________________ David Wakoff Technology Specialist CIT/Admin Systems & Distributed Technologies Cornell University E-mail: dbw2@cornell.edu 120 Maple Ave, Suite 132 Phone: (607) 254-2832 Ithaca, NY 14853 FAX: (607) 255-6982 __________________________________________________________________