Daniel Tsvetkov Daniel Tsvetkov - 2 years ago 64
SQL Question

SQL calculate DayTime and NightTime between two DateTime values

I am way over my head with an SQL problem. I have a query which makes a temporary table, fills it with data from several other tables, makes some calculations and updates and provides this data to an app. The final step to do is calculate how many hours and how many minutes there are between two datetimes, but they should be divided in dayHours, dayMins, nightHours, nightMins (datetimes can be 20+ days in between). The following bulletpoints will visualize what I want to do:

  • Let say, night time is from 23:00 to 06:00.

  • We have
    DateTime1 = 20-04-2016 13:30

  • We have
    DateTime2 = 21-04-2016 07:15

  • NightTime: from 23:00 to 06:00 = 7 hours 0 minutes.

  • DayTime: from 13:30 to 23:00 (9h30m), and then again from 06:00 to 07:15(1h15m) the following day for a total of 10 hours 45 minutes.

I am providing a
create table
query, but I only need help with the calculation so you could ignore my table and data. Note, I have erased almost all formatting to reduce, as the post got really long.

CREATE TABLE [dbo].[myTestTable](
[JHID] [int] NULL, [ToDateTime] [datetime] NULL,
[startPayDateTime] [datetime] NULL, [opDayHour] [int] NULL,
[opDayMin] [int] NULL, [opNightHour] [int] NULL,
[opNightMin] [int] NULL, ) ON [PRIMARY] GO

Consider inserting this as a test data. The columns (for test purposes) are

INSERT INTO [myTestTable]
VALUES (301533,'14-03-2016 01:54','14-03-2016 04:54',1,1,1,1),
(302488,'14-03-2016 01:54','14-03-2016 08:31',0,0,0,0),
(302676,'14-03-2016 01:54','28-03-2016 08:11',1,1,1,1) GO

So now I have to

SET opDayHour = (CASE WHEN ... THEN *value* ELSE 0 end),
opDayMin = (CASE WHEN ... THEN *value* ELSE 0 end),
opNightHour = (CASE WHEN ... THEN *value* ELSE 0 end),
opNightMin = (CASE WHEN ... THEN *value* ELSE 0 end),

How do I Thank you for your consideration, if my question is not clear enough leave a comment ! :)

Answer Source

You can use cte for that count:

@DateTime1 datetime = '2016-04-20 13:30',
@DateTime2 datetime = '2016-04-21 07:15'

;WITH times AS(
SELECT  @DateTime1 as d,
        CASE WHEN DATEPART(hour,@DateTime1) between 6 and 22 then 'd' else 'n' end as a,
        0 as m
SELECT  DATEADD(minute,1,d),
        CASE WHEN DATEPART(hour,DATEADD(minute,1,d)) between 6 and 22 then 'd' else 'n' end as a,
FROM times
WHERE DATEADD(minute,1,d) <= @DateTime2

SELECT  CASE WHEN a = 'd' THEN 'DayTime' ELSE 'NightTime' END as TimePart,
        sum(m)/60 as H,
        sum(m) - (sum(m)/60)* 60 as M
FROM times

Output be like:

TimePart  H           M
--------- ----------- -----------
DayTime   10          45
NightTime 7           0

(2 row(s) affected)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download