create the dateid table in sql server

Vineet S 1,070

Hi ,

how to populate the dateid and date in dimtime table using script as below output upto 2029 excluding saturday and sunday

create table table (dateid int,day varchar(23),

dateid | day

20240601 | 2024 June 01

20290601 | 2029 June 01

Vineet S 1,070 Reputation points

2024-07-10T15:41:19.5266667+00:00

i tried this but unabele to exclude saturday and sunday

CREATE TABLE _Dates (

d DATE,

PRIMARY KEY (d)

)

DECLARE @dIncr DATE = '20040101'

DECLARE @dEnd DATE = '20290101'

WHILE ( @dIncr < @dEnd )

BEGIN

INSERT INTO _Dates (d) VALUES( @dIncr )

SELECT @dIncr = DATEADD(DAY, 1, @dIncr )

END

select * from [_Dates]
Erland Sommarskog 112.5K Reputation points MVP

2024-07-10T16:24:08.38+00:00

You have littered your post with tags:

I would recommend that you clean it up, so that we know which platform you are using. The solutions for Databricks, MySQL and SQL Server are all likely to be different. And if you are on SQL Server, please tell us which version, since SQL 2022 offers a neat way to do this.

Accepted answer

Naomi Nosonovsky 7,856 Reputation points

2024-07-10T16:19:10.4733333+00:00
Take a look at this link https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/

If you just want to modify your code, you can add

BEGIN IF DATEPART(weekday, @dIncr) NOT IN (1,7) INSERT INTO _Dates (d) VALUES( @dIncr ) SELECT @dIncr = DATEADD(DAY, 1, @dIncr ) END
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

0 additional answers

Your answer