Creating a Date Dimension (Calendar Table) in SQL Server

Question

Creating a Date Dimension (Calendar Table) in SQL Server

Noman072

Mr or Mrs. 500

Points: 560
More actions
April 14, 2025 at 12:00 am

#4572091

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

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

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

Terry Curran Newbie Points: 2 More actions · Answer 1

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.

Jeff Moden SSC Guru Points: 1004577 More actions · Answer 2

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)

NBSteve SSCarpal Tunnel Points: 4126 More actions · Answer 3

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

MMartin1 One Orange Chip Points: 27833 More actions · Answer 4

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.

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

P Jones SSChampion Points: 12369 More actions · Answer 5

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.

Jeff Moden SSC Guru Points: 1004577 More actions · Answer 6

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)

Peter Nolan Newbie Points: 6 More actions · Answer 7

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