Experts,
Have a question regarding splitting a date range into equal parts by months including the time part
example – fromdate – 06/29/2020 09:00:00 and todate – 06/29/2021 09:00:00
Want to split this date range into twelve equal parts like below
06/29/2020 09:00:00 - 06/30/2020 12:59:59
07/01/2020 00:00:00 - 07/31/2020 12:59:59
.........
.......
06/01/2021 00:00:00 - 06/29/2021 09:00:00
I cant write recursive CTE as this is a sql synapse module I am running against..
With below query I am able to split the date part, but time part is not coming properly as above.. Please help me as this is a blocking my development
declare @FromTs DATETIME
declare @ToTs DATETIME
SET @FromTs = GetDate()
SET @ToTs = DATEADD(month, 12, @FromTs)
;WITH n(n) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY [object_id])-1 FROM sys.all_columns
),
d(qi,qrt,qtt,n,f,t,md,bp,ep,rn) AS
(
SELECT
,n.n, @FromTs, @ToTs,
DATEDIFF(MONTH, @FromTs, @ToTs),
DATEADD(MONTH, n.n, DATEADD(DAY, 1-DAY( @FromTs), @FromTs)),
DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEADD(MONTH, n.n,
DATEADD(DAY, 1-DAY( @FromTs), @FromTs))))
FROM n INNER JOIN <datetbl> AS d
ON @ToTs >= DATEADD(MONTH, n.n-1, @FromTs)
)
SELECT qi,qrt,qtt,
new_from_date = CASE n WHEN 0 THEN f ELSE bp END,
new_to_date = CASE n WHEN md THEN t ELSE ep END,rn
FROM d WHERE md >= n
Best Answer
fiddle
The range is divided to equal (in seconds) parts. You may alter the calculations granularity (for example, use
HOURS
instead ofSECOND
), parts lengths will be adjusted and rounded automatically.You may replace
numbers
CTE with any other which gets/generates the integers list from0
toparts_amount-1
, and parametrize the divident used in a query (12
, may be replaced with, for example,SELECT COUNT(num) FROM numbers
).