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...
--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.
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