Creating a Date Dimension (Calendar Table) in SQL Server

  • Comments posted to this topic are about the item Creating a Date Dimension (Calendar Table) in SQL Server

  • When I created a date dimension table, instead of calculating all the parts of the dates, I only created the date column, the rest of the columns I used calculated columns.

  • It would appear that most of the code has simply been stolen from the following article...

    https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I knew something seemed familiar.  Plagiarism's not cool  🙁

  • Plagarism , not cool.

    Just to still make a note. I find the ISO_week property interesting.

    select datepart(iso_week, '2025-03-30')

    select datepart(week, '2025-03-30')

    Maybe we need an article on this topic.

    ----------------------------------------------------

  • Agree about the plagiarism - too much of it around. We have to support several pieces of anti-plagiarism software at the University to monitor students' work.

    As for the date dimension, I may use this or rather the original code for our new AWS data lake - my original used a time dimension from SSAS as the basis.

  • MMartin1 wrote:

    Plagarism , not cool.

    Just to still make a note. I find the ISO_week property interesting.

    select datepart(iso_week, '2025-03-30') select datepart(week, '2025-03-30')

    Maybe we need an article on this topic.

    It's the same thing that causes the following...

    select datepart(week, '2024-12-31'),datepart(iso_week, '2024-12-31')
    select datepart(week, '2025-01-01'),datepart(iso_week, '2025-01-01')

    ISO_Week always starts on Monday.  The default starting day for non-normal weeks is Sunday and is also the only possibility for DATE_DIFF and DATEDIFF_BIG for non-iso weeks.

    Further, normal week counts start on the first of the year with the first week containing anywhere from 1 to 7 days.  For ISO, the first week is always a 7 day week and frequently includes days from the previous year because it always contains the first Thursday of the year.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hi Guys,

    for anyone who wants a nice date dimension, plus a very good data model, they can get it for free from my drop box on this link. This is an sql server backup of a freebie BI4ALL model instance.

    https://www.dropbox.com/scl/fi/x663a7vbaxj0v6597gr83/BI4ALL3000-DATA-BASE.zip?rlkey=92u0bidvwh5tcc04s0dzfrm1h&dl=0

    Best Regards
    Peter Andrew Nolan

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

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