4-4-5 Calendar Functions, Part 2

Question

Post reply

4-4-5 Calendar Functions, Part 2

corder

Ten Centuries

Points: 1275
More actions
January 10, 2010 at 9:04 pm

#219126

Comments posted to this topic are about the item 4-4-5 Calendar Functions, Part 2

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Cem Uney Mr or Mrs. 500 Points: 533 More actions · Answer 1

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

4-5-4 calendar...

For example if your week starts with Monday.

01/01/2010 Friday

02/01/2010 Saturday

03/01/2010 Sunday

04/01/2010 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 😉

mtassin SSC-Insane Points: 23099 More actions · Answer 2

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 🙂

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Thanks for taking the time to put together this article.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

corder Ten Centuries Points: 1275 More actions · Answer 4

Thanks for the comments. It somehow slipped past me that the article was published already. Any other comments are much appreciated.

Cliff

ajhuddy Valued Member Points: 53 More actions · Answer 5

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.

E.g.

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.

Your thoughts?

corder Ten Centuries Points: 1275 More actions · Answer 6

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.

ron.carlton 41182 SSC Enthusiast Points: 135 More actions · Answer 7

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.