Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Time - Adding minutes and seconds


Time - Adding minutes and seconds

Author
Message
tan110
tan110
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 702
I have the 2 columns with time datatypes.

select top 5 [Connect Time2],[totalTime2] from dbo.verizonDetails

Connect Time2 totalTime2
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

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.0000000 00: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.
Ray K
Ray K
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2624 Visits: 4567
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
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24282 Visits: 37989
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);



Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
WayneS
WayneS
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6265 Visits: 10404
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

tan110
tan110
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 702
You are so Awesome! Lynn. Thank very much.
tan110
tan110
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 702
Thanks so much everyone!
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45447 Visits: 39944
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45447 Visits: 39944
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24282 Visits: 37989
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.


Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24282 Visits: 37989
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);



Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search