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