4-4-5 Calendar Functions, Part 2

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Cliff Corder » 4-4-5 Calendar Functions, Part 2

4-4-5 Calendar Functions, Part 2

Rate Topic

Display Mode

Topic Options

Author

Message

corder

Posted Sunday, January 10, 2010 9:04 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, February 05, 2013 12:17 PM
Points: 36, Visits: 268

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

Post #845163

Cem Uney

Posted Monday, January 11, 2010 6:41 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, January 04, 2013 8:13 AM
Points: 45, Visits: 128

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 ;)

Post #845333

mtassin

Posted Monday, January 11, 2010 7:16 AM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 5:57 PM
Points: 3,226, Visits: 64,232

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
For tips on how to post your problems

Post #845363

SQLRNNR

Posted Monday, January 11, 2010 11:46 AM

SSCoach

Group: General Forum Members
Last Login: 2 days ago @ 1:07 PM
Points: 18,733, Visits: 12,332

Thanks for taking the time to put together this article.

Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #845603

corder

Posted Wednesday, January 13, 2010 7:11 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, February 05, 2013 12:17 PM
Points: 36, Visits: 268

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

Cliff

Post #846861

ajhuddy

Posted Sunday, February 07, 2010 9:01 PM

Forum Newbie

Group: General Forum Members
Last Login: Today @ 2:09 PM
Points: 5, Visits: 146

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?

Post #861412

corder

Posted Monday, February 08, 2010 7:35 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, February 05, 2013 12:17 PM
Points: 36, Visits: 268

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.

Post #861664

ron.carlton 41182

Posted Thursday, July 15, 2010 10:20 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, February 22, 2013 9:33 AM
Points: 37, Visits: 67

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.

Post #953299

« Prev Topic | Next Topic »

Permissions