This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(204.8, 13%, 29%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EVS%3C/text%3E%3C/svg%3E)
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
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]
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.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(137.6, 31%, 23%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ENN%3C/text%3E%3C/svg%3E)
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