Split date range (timestamp) into equal parts by Month- SQL Server Synapse

by Tech_Enthu   Last Updated June 29, 2020 22:06 PM - source

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
Tags : date split


Related Questions


PostGis: How to split lines at points using sql

Updated October 15, 2018 23:06 PM

MySql split table on insert

Updated May 05, 2019 18:06 PM

msaccess split db feature linked tables

Updated July 22, 2019 14:06 PM

Splitting a column type number

Updated June 11, 2020 15:06 PM