Tuesday, November 3, 2015

Find all Date between a range of dates

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GetPeriodsBetweenTwoPeriods]
(
@StartDate Date,
@EndDate Date
)
RETURNS @Periods TABLE (
    Period Date
)
AS
BEGIN
  --select * From dbo.GetPeriodsBetweenTwoPeriods('2012-01-01','2013-01-01')
  WITH CTE
    AS (SELECT
        @StartDate AS Period
    UNION ALL
    SELECT
        DATEADD(MONTH, 1, Period)
    FROM CTE
    WHERE DATEADD(MONTH, 1, Period) <= @EndDate)
    Insert into @Periods SELECT
        *
    FROM CTE
    ORDER BY period DESC

    RETURN

END

No comments:

Post a Comment