CREATE FUNCTION [dbo].BD_DateRange_prc
(
@StartDate datetime2,
@EndDate datetime2,
@DatePart nvarchar(3)=’dd’,
@Pas int=1
)
RETURNS TABLE AS RETURN

WITH A(A) AS (SELECT 0 FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) A(A)),
B(RowNum) AS (SELECT TOP(ABS(CASE @DatePart
WHEN ‘ns’ THEN DATEDIFF(ns, @EndDate, @StartDate)/@Pas
WHEN ‘mcs’ THEN DATEDIFF(mcs,@EndDate, @StartDate)/@Pas
WHEN ‘ms’ THEN DATEDIFF(ms, @EndDate, @StartDate)/@Pas
WHEN ‘ss’ THEN DATEDIFF(ss, @EndDate, @StartDate)/@Pas
WHEN ‘mi’ THEN DATEDIFF(mi, @EndDate, @StartDate)/@Pas
WHEN ‘hh’ THEN DATEDIFF(hh, @EndDate, @StartDate)/@Pas
WHEN ‘dd’ THEN DATEDIFF(dd, @EndDate, @StartDate)/@Pas
WHEN ‘ww’ THEN DATEDIFF(ww, @EndDate, @StartDate)/@Pas
WHEN ‘mm’ THEN DATEDIFF(mm, @EndDate, @StartDate)/@Pas
WHEN ‘qq’ THEN DATEDIFF(qq, @EndDate, @StartDate)/@Pas
WHEN ‘yy’ THEN DATEDIFF(yy, @EndDate, @StartDate)/@Pas
ELSE DATEDIFF(dd, IIF(@StartDate < @EndDate, @StartDate, @EndDate), IIF(@StartDate < @EndDate, @EndDate, @StartDate))/@Pas
END) + 1)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) – 1
FROM A A, A B, A C, A D, A E, A F, A G, A H)

SELECT CASE @DatePart
WHEN ‘ns’ THEN DATEADD(ns, T.AddAmount, @StartDate)
WHEN ‘mcs’ THEN DATEADD(mcs,T.AddAmount, @StartDate)
WHEN ‘ms’ THEN DATEADD(ms, T.AddAmount, @StartDate)
WHEN ‘ss’ THEN DATEADD(ss, T.AddAmount, @StartDate)
WHEN ‘mi’ THEN DATEADD(mi, T.AddAmount, @StartDate)
WHEN ‘hh’ THEN DATEADD(hh, T.AddAmount, @StartDate)
WHEN ‘dd’ THEN DATEADD(dd, T.AddAmount, @StartDate)
WHEN ‘ww’ THEN DATEADD(ww, T.AddAmount, @StartDate)
WHEN ‘mm’ THEN DATEADD(mm, T.AddAmount, @StartDate)
WHEN ‘qq’ THEN DATEADD(qq, T.AddAmount, @StartDate)
WHEN ‘yy’ THEN DATEADD(yy, T.AddAmount, @StartDate)
ELSE DATEADD(dd, T.AddAmount, @StartDate)
END [Value]
FROM B
CROSS APPLY(VALUES (IIF(@StartDate<@EndDate, @Pas*RowNum, @Pas*-RowNum))) T(AddAmount)