Calendar Table by Day

  • JustMarie

    SSCertifiable

    Points: 7771

    Comments posted to this topic are about the item Calendar Table by Day

  • William Soranno

    SSCommitted

    Points: 1608

    Thank you for the post, it looks interesting.

    One question.

    How do you recreate it when Sql restarts?

    Since tempdb is deleted and recreated on a restart.

    Bill Soranno
    MCP, MCTS, MCITP DBA
    Database Administrator
    Winona State University
    Maxwell 143

    "Quality, like Success, is a Journey, not a Destination" - William Soranno '92

  • JustMarie

    SSCertifiable

    Points: 7771

    Thanks for the kind words. If I can save someone else time by sharing it's worth it.

    I wrote all the calendar tables and the tally table as stored procedures. Makes them easy to recreate as long as it's in the checklist.

  • Manie Verster

    SSCertifiable

    Points: 7022

    Thanks for a great article. This is exactly what I was looking for. However, I saw one flaw (if it is a flaw).

    SET @End = (

    SELECT DATEPART(DD, DATEADD(DD, -1, DATEADD(YY, 1, @StartDate)))

    This will only give you 31 days. Looking at where you get the end of the year date DATEADD(YY, 1, @StartDate) I think you would want to populate the whole year, anyway that is what I needed so I changed it a bit so instead of the first datepart I used datediff to get 365 days.

    SET @End = (

    SELECT DATEDIFF(DD, DATEADD(DD, -1, DATEADD(YY, 1, @StartDate)))+1

    The datediff without +1 will give you 364 days and that is why I added the +1.

    This is not meant as criticism just an idea.

    :-PManie Verster
    Developer
    Johannesburg
    South Africa

    I can do all things through Christ who strengthens me. - Holy Bible
    I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

  • Manie Verster

    SSCertifiable

    Points: 7022

    Sorry, I meant to add this piece. God is good! I was going to do nested while loops and then after asking Him to help, He guided me to your article!!! Wonderful!!!

    :-PManie Verster
    Developer
    Johannesburg
    South Africa

    I can do all things through Christ who strengthens me. - Holy Bible
    I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)

  • Shakeeb.ahamed

    Grasshopper

    Points: 17

    Thanks Bayer 🙂 for providing such a good script which serve my purpose.

    I modified little get the week number and week number for the month.

    DATEPART(WEEK,Day(DATEADD(DD, (N - 1), @StartDate))) as CalMonthWeek

    ,DATEPART(WEEK,DATEADD(DD, (N - 1), @StartDate)) as CalDayWeek

  • terry.home

    SSC Eights!

    Points: 864

    Msg 208, Level 16, State 1, Line 21

    Invalid object name 'dbo.CalDay'.

    SQL Server 2005 Express (with SSMS 2008)

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

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