13

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.
3
  • 5
    I don't see how your edit to your question further clarifies the question at hand.
    – swasheck
    Jan 11, 2013 at 20:32
  • @swasheck I explicitly state the three things I am looking for. I also set bounds on what I am not looking for.
    – Trisped
    Jan 11, 2013 at 20:42

2 Answers 2

38

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.

0
-1

Simply use dateadd function to add your minutes in integer against '0:00'. Then cast back to time.

Select cast(dateadd(minute,84,'0:00') as time)

Here, 84 is the integer minute I want to be expressed in "time" type.

I added that to '0:00' and then to remove the date component, I castes it to time type. No custom coding necessary.

(No column name)

01:24:00.0000000

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.