Reputation: 426
I'm storing time as an int as seen in the Time column below.
I can convert to datetime or to seconds using already defined functions but I need to sum it after rounding to 30 minutes.
-----------------------------------------------------------------
Time | datetime | desired output as datetime
-----------------------------------------------------------------
11600 | 2021-05-05 01:16:00.000 | 2021-05-05 01:30:00.000
200 | 2021-05-19 00:02:00.000 | 2021-05-19 00:30:00.000
100 | 2021-05-10 00:01:00.000 | 2021-05-10 00:30:00.000
23000 | 2021-05-21 02:30:00.000 | 2021-05-21 02:30:00.000
-----------------------------------------------------------------
What I manage so far is convert int to datetime and then on condition round to 30 minutes (desired output column above) but I still need to sum it and can't like this:
;with t as(
SELECT
[dbo].[sys_time_from_int](DATE,TIME_AWAY) DATE_AUX
FROM [time_sheet] x1
)
SELECT
CASE
WHEN datepart(MINUTE, DATE_AUX) = 30 or datepart(MINUTE, DATE_AUX) = 00
THEN DATEADD( minute, ( DATEDIFF( minute, 0, DATEADD( second, ( 30 * 60 ) / 2, DATE_AUX) ) / 30 ) * 30, 0 )
ELSE DATEADD( minute, ( DATEDIFF( minute, 0, DATEADD( minute, 30 , DATE_AUX) ) / 30 ) * 30, 0 )
end as TIME_AUX
from t
the function seen takes (2021-05-05, 11600) and converts it to datetime. I can also use c#. any tips would be greatly appreciated.
Upvotes: 1
Views: 290
Reputation: 81930
Seems like you are aggressively rounding up (hence the +29).
Perhaps this will help
Example
Declare @YourTable Table ([Time] int,[datetime] datetime)
Insert Into @YourTable Values
(11600,'2021-05-05 01:16:00.000')
,(200,'2021-05-19 00:02:00.000')
,(100,'2021-05-10 00:01:00.000')
,(23000,'2021-05-21 02:30:00.000')
Select *
,NewValue = dateadd(minute,((datediff(minute,0,[datetime])+29) / 30) * 30,0)
From @YourTable
Results
Time datetime NewValue
11600 2021-05-05 01:16:00.000 2021-05-05 01:30:00.000
200 2021-05-19 00:02:00.000 2021-05-19 00:30:00.000
100 2021-05-10 00:01:00.000 2021-05-10 00:30:00.000
23000 2021-05-21 02:30:00.000 2021-05-21 02:30:00.000
Update with Aggregate
Select sum((datepart(hour,NewValue)*60) + datepart(minute,NewValue)) / 60.0
From @YourTable
Cross Apply (values ( ( dateadd(minute,((datediff(minute,0,[datetime])+29) / 30) * 30,0) ) ) ) b(NewValue)
Upvotes: 1