Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.
share|improve this question
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
    

1 Answer 1

up vote 18 down vote accepted

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.

share|improve this answer
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... –  Aaron Bertrand 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. –  Jack Douglas Jan 11 '13 at 6:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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