Comments posted to this topic are about the item 4-4-5 Calendar Functions, Part 2
Mr or Mrs. 500
Thanks for nice article.
Also function which returns the Weeks is more usefull for retail programs and like-for-like discussions.
As you now sql server's DATEPART weeknumber function does not return the real Week number according to the
For example if your week starts with Monday.
Sql Server returns 1 for weeknumber of 01/01/2010 but it is the 53th week of 2009.
Sql Server returns 2 for weeknumber of 04/01/2010 but it is the 1st week of 2010.
So the new function should return 2009W53 2010W01
anyway thanks again 😉
Every time I read these articles about 4-4-5 my head hurts... and I vow again and again to make sure to not take a job with a company that follows this practice 🙂
Thanks for taking the time to put together this article.
I have given a name to my pain...MCM SQL Server, MVP
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Thanks for the comments. It somehow slipped past me that the article was published already. Any other comments are much appreciated.
Thanks for the article. I'm actually working on fiscal based periods at the moment, and have opted to have all of the periods defined in a setup-type table.
Period # | Fiscal Year # | Quarter # | Start Date | End Date
That way, the end-user is responsible to define the fiscal year, and no calculations are required.
Using a table is preferable. You'll see better performance out of queries. Something interesting that came up during the comments on Part 1 of the article is performance issues with Date comparisons, especially the BETWEEN operator, and possible ways to increase performance. You might want to check that out.
A table is preferrable for performance reasons. I used a table function because i can pass in what factory location since each of our sites has a different calendar of workdays and holidays; and of course i had to revised it during development several times because the "requirements" kept changing.
Viewing 8 posts - 1 through 7 (of 7 total)