CODEWITHSUNDEEP
sql
Prasad Mari
Prasad Mari

Reputation: 163

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

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

Upvotes: 13

Views: 8041

Answers (3)

Hamidreza
Hamidreza

Reputation: 3118

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

Upvotes: 8

Jim
Jim

Reputation: 962

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

Upvotes: 0

KekuSemau
KekuSemau

Reputation: 6856

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)

Upvotes: 0

Related Questions