#Oracle week number how to#
In fact, and like many other articles on the subject, there's almost too much information on ISO Week Numbers especially when trying to figure out how to calculate the value for any given date.Īs a result of the "apparent" complexity of the problem, many people have come up with solutions ranging from 100 line monstrosities to smaller code that looks like it couldn't get any simpler. The WikiPedia article is a beautiful article with lots and lots of information on the subject. If you don't, please see the following WikiPedia article for more information: A Missed "KISS". If you're reading this article, then you probably already know what an "ISO Week Number" is and what some of the advantages of using it are. The ISO (International Standards Organization or International Organization for Standards, depending on where you hail from) came up with a remedy for that problem in the form of an "ISO Week Number". That usually leaves a partial week at both the beginning an end of the year but not always. "t-clausen.dk"! What is an "ISO Week Number"?Ĭalendars are a real pain in the patooti, especially for businesses, mostly because the calendar year can start on any day of the week. The purpose of this article is to not only share this wonderful find but to also explain how it works and to express my appreciation to the author of a really fine bit of SQL prestidigitation. Here's the link to the thread that had me trying to flatten out the high spots on my head: (look for the entry by "t-clausen.dk") What I found was an absolutely brilliant and simple formula that had me banging my head on my desk and yelling "It's SO simple! Why didn't I think of that!?" I have to tell you, it was it a bit like hitting a lottery. I had looked at several of the "iSF capable" functions and was just about to settle on using one of them but decided to look at just one more post. Yes, I know that SQL Server 2008 and up has a wonderful ISO_Week DATEPART that can be used to return the ISO Week Number as an integer, but what about folks (like me) still "stuck" with SQL Server 2005 or less? I say "very fortunately" because I couldn't believe my good luck with what I found. I wanted one that I could use as a high performance "iSF" (Inline Scalar Function) like the one I had built. Very fortunately for me, I had misplaced my previously written T-SQL solution to solve for ISO Week Numbers and decided to do a quick web search to find one instead of trying to find my own. How to build a “Broadcast Calendar” will have to wait until another article, though, because I found something else that’s very cool. SELECT was working on a thread that asked how to create a "Broadcast Calendar" ( ) and, to make a much longer story shorter, it dawned on me that I might be able to solve the problem using a calculation similar to an "ISO Week Number" calculation. The modifications only cause a scant increase of a dozen or so milliseconds over a million row test so it's still a competitor with the ISOWEEK date-part of SQL Server 2008 and up and still works in earlier versions. Here's that code and that's the one I recommend that you use if you have the need (< SQL Server 2008). SELECT verified the modified formula and made an additional optimization that removed one of the additions which saves a bit on CPU time.
#Oracle week number serial#
I found the fix and that was to add the difference in dates to the "base date" instead of using the difference in dates as a date serial number. I had the article taken "offline" until I could fix it and test it.
#Oracle week number serial numbers#
This works just fine for dates greater than or equal to because that difference in days matches SQL Server's underlying date serial numbers but it doesn't work correctly for dates that have a date serial number less than 0 (). The problem is with "Step 3 – Convert the Date Serial to the "Day of the Year" where a difference in days is used in the calculation instead of the actual date. The original formula has a problem that I didn't originally test for and, that is, if you use the formula for dates earlier than, the calculation of the correct ISOWeek is by chance rather than by plan.
This article is no exception and I thank Peter Larsson who discovered that there was a problem. I've made no changes in the article starting with the "Introduction" because it still correctly describes the original formula written by "t-clausen.dk".Īs is the nature of this fine community, if there's a problem with something in an article, it is quickly ferreted out by those who participate in the discussion.
0 kommentar(er)
