How do I add minutes to a time data type?

I have a stored procedure which inserts two records into a table, the difference between the records is that the time column of the second record is @MinToAdd after the first:

CREATE PROCEDURE CreateEntry
    /*Other columns*/
    @StartTime time(2),
    @EndTime time(2),
    @MinutesToAdd smallint
    AS
BEGIN
    SET NOCOUNT ON;

    SET @MinutesToAdd = @MinutesToAdd % 1440;   --Prevent overflow if needed?
    IF (@MinutesToAdd > 0)
    BEGIN
    INSERT INTO ClientNotification (/*Other columns*/ startTime, endTime)
        OUTPUT inserted.id
        VALUES
               (/*Other columns*/ @StartTime, @EndTime),
               (/*Other columns*/ @StartTime + @MinutesToAdd, @EndTime + @MinutesToAdd);
    END
    ELSE
    BEGIN
        /*Whatever ELSE does.*/
    END
END

What is the correct way to add @MinutesToAdd minutes to @StartTime and @EndTime?

Please note I am using the time data type.

Update:

A correct answer should contain the following information:

• How to add minutes to to a time data type.

• That the proposed solution does not result in a loss of precision.

• Issues or concerns to be aware of in the event that the minutes would be to too large to fit in a time variable, or risk of rolling the time variable over. If there are no issues then please state so.

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.

StackExchange Database Administrators Q#31669, answer score: 38