Populate table with dates

Situatie

Mai jos este o procedura ce primeste doi parametrii, o data start si o data sfarsit si pe baza lor, creaza o tabela pe care o populeaza cu toate datele carendaristicile aflate intre cele doua capete de interval. In tabela sunt populate si colane suplimentare de descriere pentru fiecare data, cum ar fi a ce zi din sapatama\luna\an este, ce saptamana a anului este, ce quarter al anului este, etc.

Solutie

— exec PopulateDateTable_prc ‘2022-01-01’, ‘2022-12-31’

alter procedure PopulateDateTable_prc
(
@startdate date,
@enddate date
)

IF NOT EXISTS (SELECT * FROM sysobjects WHERE NAME = ‘MyDates’)
CREATE TABLE MyDates
(
DateID INT NOT NULL
,TheDate DATETIME NOT NULL
,DayOfMonth INT NOT NULL
,DayOfYear INT NOT NULL
,DayOfWeek INT NOT NULL
,DayName VARCHAR(9) NOT NULL
,WeekOfYear INT NOT NULL
,WeekName VARCHAR(7) NOT NULL
,MonthOfYear INT NOT NULL
,MonthName VARCHAR(9) NOT NULL
,Quarter INT NOT NULL
,QuarterName CHAR(6) NOT NULL
,Year INT NOT NULL

)

DECLARE @date DATETIME
DECLARE @id INT

SET @id=0
SET @date=DATEADD(dd, @id, @startdate)

WHILE @date <= @enddate
BEGIN
INSERT INTO MyDates
VALUES (@id–DateID
,@date–TheDate
,DATEPART(dd, @date)–DayOfMonth
,DATEPART(dy, @date)–DayOfYear
,DATEPART(dw, @date)–DayOfWeek
,DATENAME(dw, @date)–DayName
,DATEPART(ww, @date)–WeekOfYear
,’Week ‘ + DATENAME(ww, @date)–WeekName
,DATEPART(mm, @date)–MonthOfYear
,DATENAME(mm, @date)–MonthName
,DATEPART(qq, @date)–Quarter
,’Q’ + DATENAME(qq, @date) + DATENAME(yy, @date)–QuarterName
,DATEPART(yy, @date)–Year
)

SET @id=@id + 1
SET @date=DATEADD(dd, @id, @startdate)

END

select * from MyDates
order by 1

Tip solutie

Permanent

Follow Us