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
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)

Tip solutie

Permanent

Voteaza

(10 din 20 persoane apreciaza acest articol)

Despre Autor

Leave A Comment?