One issue I have run across in the past is how the Day of Week numeric representation is calculated by SQL Server. The problem stems from the fact that different countries have different interpretations of what consitutes the first day of the week. For example, Monday is considered the first day of the week for some while others consider Sunday the first day of the week.
To handle this, SQL Server has a global variable @@DateFirstSetting that can be used to specify the first day of the week.
Of course, when creating an application that uses day of week logic and can be used internationally, this can lead to some problems.
I have standardized PolyMon 2.x on using Monday as the first day of the week. However, people could still be using different @@DateFirstSetting values, so I needed a way to avoid using SQL's internal day of week function.
To this end I created a small table (DayOfWeek) that stores the day of the week along with their internal id and other bits of information such as Long, Medium and Short text representations that will be used in the PolyMon front-end. Then, a function (dt_StdDOW) is provided that will calculate the day of the week for any specified date based on that table, irrespective of the @@DateFirstSetting value.
A script to generate the table and function, as well as populate data into the table is available here.
This blog is all about the PolyMon project and related technologies.