Sharepoint ISO Week Number

Finding the week number for a date in Sharepoint cand be a real pain in the ass. The Internet provides some solutions, but they don't adhere to the ISO standard concerning week numbers:

 - weeks start on monday

 - the first week of the year is the one containing the fist Thursday of the year

The starting point for the formula below can be found here. It's pretty complex, and I suggest that first you should build some helper calculated functions for the most used values: day of week (DOW) of the current date, DOW of the first day of the current year, current day/month/year.

And here it is (replace "the_date" with the name of your date column):

=INT(

    IF(AND(MONTH([the_date])=1,

           5 < WEEKDAY(DATE(YEAR([the_date]),1,1)), 

           WEEKDAY(DATE(YEAR([the_date]),1,1)) < 9 - (DAY([the_date])-1)),

      

      (IF(WEEKDAY(DATE(YEAR([the_date]),1,1)) < 6,1,0)) + 

      44 +

      (59+WEEKDAY(DATE(YEAR([the_date])-1,1,1))-WEEKDAY(DATE(YEAR([the_date]),1,1)))*9 / 64,

      

      IF(AND(MONTH([the_date])=12,

            30-(DAY([the_date])-1) < WEEKDAY(DATE(YEAR([the_date])+1, 1, 1)) - 2,

            WEEKDAY(DATE(YEAR([the_date])+1,1,1)) < 5),

         

         1,

         

         (IF(WEEKDAY(DATE(YEAR([the_date]),1,1)) < 6,1,0)) +

         4*(MONTH([the_date])-1) +

         (2*(MONTH([the_date])-1)+(DAY([the_date])-1)+WEEKDAY(DATE(YEAR([the_date]),1,1))-WEEKDAY([the_date])+6)*36/256)))

... I know, it's horrible... if you have time, make it pretty...

Death to the ORMs

“Necessary evil” is just an excuse for incompetence in finding a solution for a hard problem. There is a long history of applications that didn’t require the user to rewrite the database schema in order to access the data: xBase, MS Access, SSMS, LINQPad. I can’t stand the nonsense surrounding the so called solutions for accessing data from a database, not counting the religious wars on what’s the best ORM. They’re all crap.

Most programming languages, especially the OO breed, are not adapted to data manipulation as good as the plain old SQL. Microsoft made some progress on this front with LINQ and the Entity framework. But that’s not enough, and it has a fundamental flow: it should have been started as a “Set-Based”, not “Entity” framework.

So, a decent set of requirements that an acceptable development platform for enterprise data applications should provide to its users is:

• set based data access
• standard GUI toolkit, geared towards data manipulation (NOT web based)
• standard business logic specifications
• database based security

Subject for the next post: how crappy are web based data applications.