Posts
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.
©2009 PolyMon
3 comments:
You could also use the following to set what the first day of the week is for your session.
SET DATEFIRST 1
SELECT @@DATEFIRST
Then you wouldn't need to use custom functions to determine the day of the week. You could also use the built in functions for date names.
Of course it might be with how you are planing to use this, it would still be easier to use your function and table.
Thanks
You are correct that I could have used a SET DATEFIRST command to change the @@DATEFIRST value. Indeed I do use this when working in projects where I have full control on the database sql scripting.
But the problem lies in that someone may very well have a different @@DATEFIRST value that is the default in their database or that they use in their own stored procedures/sql scripts. If I were to change the @@DATEFIRST value, a global variable, that would affect all running scripts on the same connection.
So setting the global @@DATEFIRST value inside one of the PolyMon stored procs could have unwanted side effects (even if setting it back to its original value before exiting) and is therefore less desirable.
The other drawback is that anyone writing sql scripts against the PolyMon database utilizing Day of Week calculations would have to remember to set their @@DATEFIRST value correctly. This way, since DoW is contained in a lookup table with RI there is less chance of this mistake being made.
SET DATEFIRST is actually a session level change, not a global change. If you don't set it back it doesn't affect any other sessions. But you are right that it might be easier if you use your own lookup table and functions. So either way, it should work fine.
Post a Comment