SQL Server , split a time duration row by 24 hour period

Question

I'm working on sql server 2008 r2. I'm trying to split a row by 24 hours period ranges between FromDate, Todate. For example, if a time row has given as below, (Range between FromDate, Todate is 4 days, so I want 4 rows)

ID   FromDate                 Todate     
---- ------------------------ -------------------------
1    2014-04-01 08:00:00.000  2014-04-04 12:00:00.000

The result I want to see like this:

ID   FromDate                 Todate                   DateDiff(HH)
---- ------------------------ -----------------------------------
1    2014-04-01 08:00:00.000  2014-04-01 23:59:59.000  15
1    2014-04-02 00:00:00.000  2014-04-02 23:59:59.000  23
1    2014-04-03 00:00:00.000  2014-04-03 23:59:59.000  23
1    2014-04-04 00:00:00.000  2014-04-04 12:00:00.000  12

FromDate `2014-04-01 08:00:00.000` is true or you mean `2014-04-01 00:00:00.000`? — Hamidreza, Mar 16 '14 at 10:16
Search for CTE date on this site, you'll find something similar. — Tony Hopkinson, Mar 16 '14 at 10:34

Hamidreza · Accepted Answer · 2014-03-16T12:54:49.203

8

Try this query:

WITH TAB1 (ID,FROMDATE,TODATE1,TODATE) AS
(SELECT ID,
FROMDATE,
DATEADD(SECOND, 24*60*60 - 1, CAST(CAST(FROMDATE AS DATE) AS DATETIME)) TODATE1,
TODATE 
FROM TABLE1
UNION ALL
SELECT 
ID,
DATEADD(HOUR, 24, CAST(CAST(TODATE1 AS DATE) AS DATETIME)) FROMDATE,
DATEADD(SECOND, 2*24*60*60-1, CAST(CAST(TODATE1 AS DATE) AS DATETIME)) TODATE1,
TODATE
FROM TAB1 WHERE CAST(TODATE1 AS DATE) < CAST(TODATE AS DATE)
),
TAB2 AS
(SELECT ID,FROMDATE,
CASE WHEN TODATE1 > TODATE THEN TODATE ELSE TODATE1 END AS TODATE
FROM TAB1)
SELECT TAB2.*,
DATEPART(hh, TODATE) - DATEPART(hh, FROMDATE) [DateDiff(HH)] FROM TAB2;

SQL Fiddle

edited Mar 16 '14 at 12:54

answered Mar 16 '14 at 11:37

Hamidreza

3,038
1
18
15

It's working great, thanks a lot, how can I show DATEDIFF(HH) also in separate column?. Please help me for this. – Prasad Mari Mar 16 '14 at 12:38
You are welcome my friend. i edit my query and it shows `DATEDIFF(HH)` as you want. – Hamidreza Mar 16 '14 at 12:53

score 0 · Answer 2 · edited May 23 '17 at 12:31

http://sqlfiddle.com/#!6/36452/2

This should be what you need. The first part of the query is a recursive CTE. This can be used to create the dates-data.

WITH CTEDays AS
(
    SELECT CONVERT(date, '20140301') datevalue
 UNION ALL
    SELECT DATEADD(day, 1, C.datevalue)
    FROM CTEDays C
    WHERE DATEADD(day, 1, C.datevalue) <= '20140501'
)
SELECT 
    R.FromDate [FromOriginal]
    , R.ToDate [ToOriginal]
    , CDays.datevalue [RawDate]
    , WantedFrom = 
        CASE WHEN CONVERT(date, R.FromDate) = CDays.datevalue THEN R.FromDate
             ELSE CDays.datevalue
        END
    , WantedTo = 
        CASE WHEN CONVERT(date, R.ToDate) = CDays.datevalue THEN R.ToDate
             ELSE DATEADD(minute, 24*60-1, CONVERT(datetime, CDays.datevalue))
        END
FROM 
    tblRange R
    OUTER APPLY
    (
        SELECT C.datevalue
        FROM CTEDays C
        WHERE C.datevalue BETWEEN CONVERT(date, R.FromDate) AND CONVERT(date, R.ToDate)
    ) CDays

However, I would suggest to set up a calendar table for this, if the CTE is too slow or inconvenient.

(as I have suggested here)

score 0 · Answer 3 · answered Mar 16 '14 at 11:35

This should get you what you need. Just remember to set maxrecursion if you are using large date ranges in from/to range.

http://sqlfiddle.com/#!6/68b32/233

declare @tblTemp TABLE (id int identity, fromDate datetime, toDate datetime)

insert @tblTemp(fromDate, toDate)
select '2014-01-01 12:00:00', '2014-01-02 12:00:00'
union select '2014-04-01 12:00:00', '2014-04-02 12:00:00'


;with cte(i, f, t, e) as (
    select id as i, fromDate as f, DATEADD(SS, -1, DATEADD(HH, 1, fromDate)) as t, toDate as e
    from @tblTemp
    union all
    select cte.i as i, DATEADD(SS, 1, cte.t), DATEADD(HH, 1, cte.t) as t, cte.e as e
    from cte
    where DATEADD(HH,1,cte.t) <= cte.e
)
select i, f, t 
from cte
order by i,f

SQL Server , split a time duration row by 24 hour period

3 Answers3

SQL Fiddle

Linked