Date Array
A table valued function that returns one row per day between a start and end date.
A simple table valued function that returns every date between a start and an end date, one row per day. It is handy for filling calendar gaps - for example joining it to your data so that days with no activity still show up as zero rows in a report.
Example: SELECT * FROM dbo.DateArray('2024-01-01', '2024-01-05')
Returns:
| Value |
|---|
| 2024-01-01 00:00:00 |
| 2024-01-02 00:00:00 |
| 2024-01-03 00:00:00 |
| 2024-01-04 00:00:00 |
| 2024-01-05 00:00:00 |
CREATE FUNCTION dbo.DateArray
(
@StartDate DateTime,
@EndDate DateTime
)
RETURNS @Array TABLE
(
Value DateTime
)
AS
BEGIN
DECLARE @Date DateTime SET @Date = @StartDate
IF(@EndDate > @StartDate)
BEGIN--IF
WHILE(@Date <= @EndDate)
BEGIN--WHILE
-------------------------------------------------------------------------------------------
INSERT INTO @Array(Value) SELECT @Date
SET @Date = @Date + 1
-------------------------------------------------------------------------------------------
END--WHILE
END--IF
RETURN
END
GO