Daniel Tsvetkov - 6 months ago 13x
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
`startPayDateTime`
and
`ToDateTime`

``````INSERT INTO [myTestTable]
([JHID],[ToDateTime],[startPayDateTime],[opDayHour],[opDayMin],[opNightHour],[opNightMin])
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

``````UPDATE
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),
``````

You can use cte for that count:

``````DECLARE
@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
UNION ALL
CASE WHEN DATEPART(hour,DATEADD(minute,1,d)) between 6 and 22 then 'd' else 'n' end as a,
FROM times
)

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
GROUP BY a
OPTION (MAXRECURSION 0)
``````

Output be like:

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

(2 row(s) affected)
``````