## Date calendaristice intr-un interval ales

Configurare noua (How To)

### Situatie

Am facut o functie ce returneaza datele calendaristice pe un interval, in diferite feluri.

• Functia primeste 4 parametri: StartDate, EndDate, DatePart, Pas
• Functia returneaza, pentru intervalul dat de primii doi parametri, toate datele calendaristice de tipul dat de parametrul DatePart si urmand pasul dat de parametrul Pas.
• Doua exemple de rulare a functiei:

— SELECT * FROM [dbo].[BD_DateRange_prc](‘2015-01-01 12:24:35’, ‘2015-02-01 12:24:35’, ‘ss’, 1)
— SELECT COUNT(1) FROM [dbo].[BD_DateRange_prc](‘2019-01-01 00:00:00’, ‘2019-01-28 20:30:20.100’, ‘ms’, default).

### Solutie

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