Week of Year function 1

[KrisSimonis]

Is there any way to change the default way SQL server determines the week of the year?
From what I've read in the help files, the first week of any year is defined as being the week in which Jan 1st is in. However I want to determine the week of year by the ISO standard ( Week 1 = the first week which has at least 4 days in this year ). Is there any way to do this easily?
( not resorting to user functions/stored procedures if possible )

Kris Simonis
OGD Software

 

[tlbroadbent]

There is no setting or option that provides the ISO week. You have a couple of options. In SQL 2000, you can create a UDF to return the ISO week. SQL 2000 BOL contains the code for such function under the topic - CREATE FUNCTION. I love ready made code!

http://msdn.microsoft.com/library/en-us/tsqlref/ts_create_7r1l.asp

If you are a SQL purist or don't yet have SQL 2000, you can create a table of dates. Each date would have the appropriate week number in another column. You could include other columns such as company fiscal week and month, IsHoliday, etc. This would make the calendar table useful for many applications. There would be no SPs or UDFs to call. You'd just do a simple join to the calendar table. This is a very appealing option if dealing with lots of records where repeated functrion calls could slow processing. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[KrisSimonis]

Thanks a lot, espec. for pointing me to that code,
I was already working on writing it myself.
'nother star for you.