How do I add minutes to a time data type?
You can't use lazy shorthand arithmetic with the new types. Try:
DATEADD(MINUTE, @MinutesToAdd, @StartTime)
Note that even though you have protected your @MinutesToAdd
from overflow, you haven't protected the result from overflow. This doesn't yield an error, however, just might not be the result you're expecting.
DECLARE @StartTime TIME(0) = '23:59';
DECLARE @MinutesToAdd INT = 20;
SELECT DATEADD(MINUTE, @MinutesToAdd, @StartTime);
Result:
00:19:00
I assume this must go through some type of internal conversion, because you couldn't get that result by saying:
DECLARE @StartTime TIME(0) = '24:19';
Result:
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
You need to consider how you want to handle calculations that lead to either @EndTime
or both @StartTime
and @EndTime
to be in the next day.
Also - to address another new requirement in your "ideal answer" - there is no loss of precision. As per the documentation, the return type of DATEADD
is the same as the input:
The return data type is the data type of the date argument, except for string literals.
Therefore, TIME
in, TIME
out.