Time - Adding minutes and seconds

Question

Time - Adding minutes and seconds

tan110

Hall of Fame

Points: 3855
More actions
January 27, 2010 at 11:06 am

#215925

I have the 2 columns with time datatypes.
select top 5 [Connect Time2],[totalTime2] from dbo.verizonDetails
Connect Time2 totalTime2
08:05:44.000000000:13:00.0000000
08:05:57.000000000:01:00.0000000
09:07:42.000000000:03:00.0000000
09:07:46.000000000:09:00.0000000
09:08:08.000000000:01:00.0000000
I want to add the time from totalTime2 to column [Connect Time2]. The desired result to look like this:
Connect Time2 totalTime2 endTime
08:05:44.000000000:13:00.0000000 08:18:44.000000
trying to run the following:
select top 5 [Connect Time2],[totalTime2], ([Connect Time2] + [totalTime2]) as endTime
from dbo.verizonDetails
but getting error:
Operand data type time is invalid for add operator.

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply

Ray K SSC-Dedicated Points: 31163 More actions · Answer 1

Go to BOL and look up DATEADD.

I just looked it up myself. You're going to need to split the hours, minutes, and seconds to use DATEADD (this can be done using time functions such as HOUR, MIN, etc.).

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Does the following help?

declare @ TimeVal time,

@ TotalTime time; -- spaces between @ and rest of variable names to allow posting of code

set @TimeVal = '08:05:44.0000000';

set @TotalTime = '00:13:00.0000000'

select @TimeVal, @TotalTime, dateadd(ss, ((DATEPART(HOUR,@TotalTime) * 60) + DATEPART(MINUTE,@TotalTime) * 60) + DATEPART(SECOND,@TotalTime), @TimeVal);

WayneS SSC Guru Points: 95460 More actions · Answer 3

This should do the trick:

-- note how this starts off by creating a table structure and putting data into it?

-- In order to get people willing to help, this is a prerequisite on your part.

-- see the link in my signature for how to do this.

declare @VerizonDetails table ([Connect Time2] TIME, [totalTime2] TIME)

insert into @VerizonDetails

values ('08:05:44.0000000', '00:13:00.0000000'),

('08:05:57.0000000', '00:01:00.0000000'),

('09:07:42.0000000', '00:03:00.0000000'),

('09:07:46.0000000', '00:09:00.0000000'),

('09:08:08.0000000', '00:01:00.0000000')

-- add the two times together, first converting them to datetimes

select *, convert(time, CONVERT(datetime, [Connect Time2]) + CONVERT(datetime, totalTime2))

from @VerizonDetails

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

tan110 Hall of Fame Points: 3855 More actions · Answer 4

tan110

Hall of Fame

Points: 3855

January 27, 2010 at 11:26 am

#1109473

You are so Awesome! Lynn. Thank very much.

tan110 Hall of Fame Points: 3855 More actions · Answer 5

tan110

Hall of Fame

Points: 3855

January 27, 2010 at 11:26 am

#1109474

Thanks so much everyone!

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 6

I know the problem has already been solved but DATEADD isn't the solution you need here because that requires that you do split the data into its various time components. All you need to do is convert the date and time columns to the DATETIME datatype and add them together using a plain ol' "+" sign.

After that, format the result the way you want it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 7

Ah... sorry Wayne... didn't see your post which is the same as what I just suggested.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Like this?

declare @ TimeVal time,

@ TotalTime time; -- space added between @ and variable name to allow code to post

set @TimeVal = '08:05:44.0000000';

set @TotalTime = '00:13:00.0000000'

select @TimeVal,

@TotalTime,

dateadd(ss, ((DATEPART(HOUR,@TotalTime) * 60) + DATEPART(MINUTE,@TotalTime) * 60) + DATEPART(SECOND,@TotalTime), @TimeVal),

@TimeVal + @TotalTime;

I get the following error:

Msg 8117, Level 16, State 1, Line 6

Operand data type time is invalid for add operator.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Nevermind! I figured it out!

declare @ TimeVal time,

@ TotalTime time; -- space added between @ and variable name to allow code to post

set @TimeVal = '08:05:44.000';

set @TotalTime = '00:13:00.000';

select @TimeVal,

@TotalTime,

dateadd(ss, ((DATEPART(HOUR,@TotalTime) * 60) + DATEPART(MINUTE,@TotalTime) * 60) + DATEPART(SECOND,@TotalTime), @TimeVal),

convert(time, (convert(datetime, @TimeVal, 108) + convert(datetime, @TotalTime, 108)), 108);

Nayeem Mansoori Valued Member Points: 60 More actions · Answer 10

We sincerely thank you..

It Saves a lot of Efforts of mine.:-)

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 11

neevmansoori (12/22/2012)
We sincerely thank you..
It Saves a lot of Efforts of mine.:-)

Yeah, but as old as this post is, I'm not sure that it answers what needs to be done for display purposes if the following data is present...

Connect Time2 totalTime2

23:00:00.0000000 01:00:00.0000000

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dwain Camps SSC Guru Points: 86948 More actions · Answer 12

Jeff Moden (12/22/2012)
neevmansoori (12/22/2012)
We sincerely thank you..
It Saves a lot of Efforts of mine.:-)
Yeah, but as old as this post is, I'm not sure that it answers what needs to be done for display purposes if the following data is present...
Connect Time2 totalTime2
23:00:00.0000000 01:00:00.0000000

I think what Jeff is alluding to without saying it is the TIME data type is a "time of day" and not a "time duration." Adding them together implies that at least one of the values is a "time duration." How can you add 4PM + 6PM?

Perhaps though, it has some meaning in another multiverse.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 13

What I'm actually trying to get people to realize is that using JUST the time for this type of stuff (EndTime = StartTime + Duration) is the wrong thing to do unless it's ok to "wrap the clock" after midnight. If you have a duration that exceeds 24 hours starting just before midnight of one day, then you really need to show that the call or whatever duration your trying to portray ended two days later just after midnight.

In other words, both StartTime and EndTime must show the date AND the time for both.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Dwain Camps SSC Guru Points: 86948 More actions · Answer 14

Jeff Moden (12/23/2012)
What I'm actually trying to get people to realize is that using JUST the time for this type of stuff (EndTime = StartTime + Duration) is the wrong thing to do unless it's ok to "wrap the clock" after midnight. If you have a duration that exceeds 24 hours starting just before midnight of one day, then you really need to show that the call or whatever duration your trying to portray ended two days later just after midnight.
In other words, both StartTime and EndTime must show the date AND the time for both.

Of course. Sorry Jeff, I didn't mean to speak for you.

But of interest, what is the meaning in the physical world of the following addition?

2012-12-21 05:00 + 2012-12-24 07:00

If your answer is that you're adding the number of days between Jan 1, 1900 and Dec 12, 2012 (+ 7 hours) to the date Jan 21, 2012 (at 5AM), I'm with you. I'm just saying it's a kludge (albeit a widely used one). Because MS hasn't been so kind to provide a specific type for a date/time interval.

Personally, my preference is to use the DATETIME to hold the date and an INT (or BIGINT) to hold the minutes, or if need be seconds, to represent the interval. Ignoring for the moment the issues you might encounter using BIGINT.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St