Geek Answers Handbook

SQL Server, divide the length of time string by a 24-hour period

I am working on sql server 2008 r2. I am trying to split the string for 24 hours between the ranges FromDate , Todate . For example, if the temporary line is below, (the range between FromDate , Todate is 4 days, so I want 4 lines)

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

As a result, I want to see the following:

 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
+8
sql
source share
3 answers

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

+5
source share

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

This should be what you need. The first part of the request is a recursive CTE. This can be used to create date 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 setting up a calendar table for this if the CTE is too slow or inconvenient.

(as I suggested here )

0
source share

This should get what you need. Remember to set maxrecursion if you use large date ranges in the range of / to the 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
0
source share

More articles:


All Articles