4-4-5 Calendar Functions, Part 2

  • corder

    Ten Centuries

    Points: 1275

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

  • Cem Uney

    Old Hand

    Points: 393

    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: 23035

    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: 281210

    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

    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

    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

    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

    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 8 (of 8 total)

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