2

Using SQL Server 2008 I would like to count the amount of minutes that land between 16:30 and 18:00 for a given date range.

So inputs are for example @fromdate = '2017-04-17 17:00:00' and @todate = '2017-04-19 17:00:00'

So for the above example using lamens maths.

Day 1 (17th) : 60
Day 2 (18th) : 90
Day 3 (19th) : 30

Total 180

Day 1 is 60 because there is 60 minutes between the start date 17:00 and 18:00 Day 2 is 90 because the middle date must be the full 90 minutes between 16:30 and 18:00 Day 3 is 30 because there is 30 minutes between 16:30 and 17:00 (the time of the end date) So I am searching for the ammount of minutes between 16:30 and 18:00 for each day if that makes sense.

I would like the total to be the output.

David H
  • 71
  • 8
  • possible dublicate of http://stackoverflow.com/questions/7709803/javascript-get-minutes-between-two-dates – mtizziani Apr 19 '17 at 11:45
  • What output do you want? The total or the breakdown by day? – Gordon Linoff Apr 19 '17 at 11:45
  • Thats a javascript question but I will look at the method and see if it helps. – David H Apr 19 '17 at 11:47
  • @GordonLinoff The total output, I have amended my question. – David H Apr 19 '17 at 11:47
  • @DavidH Please look into the solution I have sent. That will help you in getting the minutes between 2 timestamps. You can format the date to your wish.. – ankit Apr 19 '17 at 11:50
  • I feel I don't understand the question. Are you wanting to measure just the number of minutes between the times as if they were on the same date? I don't understand why in your worked example, the first 60 minutes are allocated to the 17th, the next 90 minutes to the 18th and the final 30 to the 19th. – Steve Lovell Apr 19 '17 at 12:04
  • Hi @SteveLovell, not the easiest to explain. Day 1 is 60 because there is 60 minutes between the start date 17:00 and 18:00 Day 2 is 90 because the middle date must be the full 90 minutes between 16:30 and 18:00 Day 3 is 30 because there is 30 minutes between 16:30 and 17:00 (the time of the end date) So I am searching for the ammount of minutes between 16:30 and 18:00 for each day if that makes sense. – David H Apr 19 '17 at 12:11
  • Ah, okay, I understand now. I can't answer now (at work), but perhaps your clarification will help others provide an answer. I'll submitted a suggested edit to your question. – Steve Lovell Apr 19 '17 at 12:13
  • @SteveLovell I have added that snippet to my question. Thanks – David H Apr 19 '17 at 12:28
  • Excellent. I couldn't submit an edit anyway, as the "queue" was full. Also I think you mean "layman's maths". I briefly wondered if "lamens maths" was a particular branch of mathematics. – Steve Lovell Apr 19 '17 at 12:29

2 Answers2

2

This will work in sql server. We populate our start and end datetimes in a cte. After that, it is as simple as summing the date difference in minutes:

declare @fromdate datetime = convert(datetime,'2017-04-17 17:00:00',20)
declare @todate datetime = convert(datetime,'2017-04-19 17:00:00',20)

declare @startRangeTime time = '16:30';
declare @endRangeTime time = '18:00';

with dates as (
    select top (datediff(day, @fromdate, @toDate) + 1)
    case when dateadd(day,row_number() over  (order by a.object_id) - 1, cast(@fromdate as date)) = cast(@fromdate as date)
        then @fromdate
        else cast(dateadd(day,row_number() over  (order by a.object_id) - 1, cast(@fromdate as date)) as datetime) + cast(@startRangeTime as datetime)
    end as startDt,
    case when dateadd(day,row_number() over  (order by a.object_id) - 1, cast(@fromdate as date)) = cast(@todate as date)
        then @todate
        else cast(dateadd(day,row_number() over  (order by a.object_id) - 1, cast(@fromdate as date)) as datetime) + cast(@endRangeTime as datetime)
    end as endDt
    from sys.all_objects a
    cross join sys.all_objects b
)

select sum(datediff(mi, startDt, endDt))  from dates

returns:

180

generating dates without a loop taken from this series on generating a sequence without loops

Steve Lovell
  • 2,564
  • 2
  • 13
  • 16
  • Hi thanks for your input, im getting the following Msg 195, Level 15, State 10, Line 4 'timefromparts' is not a recognized built-in function name. Msg 195, Level 15, State 10, Line 5 'timefromparts' is not a recognized built-in function name. Msg 137, Level 15, State 2, Line 11 Must declare the scalar variable "@startRangeTime". – David H Apr 19 '17 at 13:44
  • Hi @DavidH - I forgot that TIMEFROMPARTS does not exist in 2008 - I am working on a fix –  Apr 19 '17 at 14:04
  • 1
    You can get by with just assigning them like this `= '16:30'`. I submitted an edit. – Steve Lovell Apr 19 '17 at 14:18
0

You cannot subtract 2 strings so do it this way. The sample code to get the time in minutes between 2 dates.

ALTER SESSION SET NLS_LANGUAGE="American" 
WITH source as (
    SELECT '30-OCT-2016 21:08:34' as A, '30/10/2016 21:06:34' as B
    FROM Dual
)
SELECT TO_DATE(A, 'DD-MON-YYYY HH24:MI:SS') as new_a,
   TO_DATE(B, 'DD/MM/YYYY HH24:MI:SS') as new_b,
   (  TO_DATE(A, 'DD-MON-YYYY HH24:MI:SS') 
    - TO_DATE(B, 'DD/MM/YYYY HH24:MI:SS')
   ) * 24 * 60 as result
FROM source

Hope this helps...

ankit
  • 154
  • 1
  • 11
  • Hi ankit, it is not the total minutes between two timestamps I'm looking for it is the amount of minutes that are between 16:30 and 18:00 between two datetime stamps. See my example in the question. – David H Apr 19 '17 at 11:52
  • Working on the procedure call for the same. Wait for some time..! – ankit Apr 19 '17 at 11:54
  • This is a SQL code if it does not run on your server you can always change the syntax, logic does not change.!!! But still this is not what is intended and I am already working on it. – ankit Apr 19 '17 at 12:55