Date Chaos – Creating a viable date dimension table from any table that you have in the database

Would you like to know how many Tuesdays are in 2024 and 2025 using a random table from the Adventureworks Database?

No?

Me neither.

But here we go.

Consider this piece of code:

USE AdventureWorks2022;

SELECT date_x, 

day,

month,

year,

DATENAME(dw,date_x) as day_of_the_week

FROM (

SELECT RowNumber, 

CAST(DATEADD(DAY,RowNumber,'2021-12-31') as date) as [DATE_X]

, DAY(CAST(DATEADD(DAY,RowNumber,'2021-12-31') as date)) as [DAY]

, MONTH(CAST(DATEADD(DAY,RowNumber,'2021-12-31') as date)) as [MONTH]

, YEAR(CAST(DATEADD(DAY,RowNumber,'2021-12-31') as date)) as [YEAR]

FROM (

SELECT

ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RowNumber

FROM Production.BillOfMaterials

) as x

) as y

where date_x between '2024-01-01' and '2025-12-31'

and DATENAME(dw,date_x)  = 'Tuesday'

We are selecting from one table: Production Bill of Materials.

There are ~ 2k records in that table, if we roll a rownumber with a blank partition we can create a sequence.

From there we can issue a dateadd command against the count so that each ensuing row will be the next date.

From there it is just a simple where clause to tell us that there are 105 Tuesdays in the years 2023 and 2024.

52 x 2 = 104 – checks out

So if you are going to an OLAP cube and want a date dimension table, don’t buy one from Kaggle, just run this query against one of the bigger tables in your database and Bob is your uncle.

Don’t forget to pay the lady.