SQL Server 2008 – How to Add Minutes to a Time Data Type

sql serversql-server-2008

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.

Best Answer

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.

Related Question