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:

    /*Other columns*/
    @StartTime time(2),
    @EndTime time(2),
    @MinutesToAdd smallint

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

What is the correct way to add @MinutesToAdd minutes to @StartTime and @EndTime?
Please note I am using the time data type.

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.
  • 5
    I don't see how your edit to your question further clarifies the question at hand.
    – swasheck
    Jan 11 '13 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 '13 at 20:42

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);



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';


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.

  • 1
    +1 @Aaron Alternatively you can convert StartTime and TimeToAdd to datetime and then add. Converting TimeToAdd will be very messy when the minutes are > 59. DATEADD is the best solution.
    – brian
    Jan 10 '13 at 4:48
  • If you add that DATEADD returns the same type as the date argument then I will accept. The "Prevent overflow if needed?" line is not needed. The roll over issue will be handled by the source of the data and the destination of the data.
    – Trisped
    Jan 10 '13 at 19:45
  • 3
    @Trisped sure, if you add to the question that you didn't think DATEADD was appropriate because you thought it could only return DATETIME and that that could cause problems. Otherwise it doesn't seem relevant to your question or for future readers... Jan 10 '13 at 19:47
  • How is the relevance not implied by the "Please note I am using the time data type."? Also, why did you change my question to inconstantly use row instead of record?
    – Trisped
    Jan 10 '13 at 20:02
  • 1
    @Trisped the edit uses better terminology and please see the faq on editing—lets not have any more back-and-forth or I will lock the question. Aaron is right that we need to make everything clear for others, you have your answer. Please consider editing your question along the lines he suggests if you think it would be helpful: Aaron has kindly offered to add the information you want to his answer if you do. Jan 11 '13 at 6:47

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)


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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