Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

4-4-5 Calendar Functions, Part 2 Expand / Collapse
Author
Message
Posted Sunday, January 10, 2010 9:04 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, January 25, 2014 12:27 PM
Points: 39, Visits: 295
Comments posted to this topic are about the item 4-4-5 Calendar Functions, Part 2


Post #845163
Posted Monday, January 11, 2010 6:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 23, 2014 12:21 AM
Points: 45, Visits: 134
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
Posted Monday, January 11, 2010 7:16 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 12:05 PM
Points: 3,569, Visits: 72,411
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
Posted Monday, January 11, 2010 11:46 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:49 PM
Points: 20,462, Visits: 14,092
Thanks for taking the time to put together this article.



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


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
Posted Wednesday, January 13, 2010 7:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, January 25, 2014 12:27 PM
Points: 39, Visits: 295
Thanks for the comments. It somehow slipped past me that the article was published already. Any other comments are much appreciated.

Cliff



Post #846861
Posted Sunday, February 07, 2010 9:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 19, 2013 1:22 PM
Points: 5, Visits: 150
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
Posted Monday, February 08, 2010 7:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, January 25, 2014 12:27 PM
Points: 39, Visits: 295
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
Posted Thursday, July 15, 2010 10:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 24, 2013 3:24 PM
Points: 37, Visits: 68
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 »

Add to briefcase

Permissions Expand / Collapse