Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Time - Adding minutes and seconds Expand / Collapse
Author
Message
Posted Wednesday, January 27, 2010 11:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 20, 2014 8:32 PM
Points: 148, Visits: 645
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.
Post #854581
Posted Wednesday, January 27, 2010 11:14 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, November 20, 2014 7:01 AM
Points: 2,050, Visits: 3,525
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.).
Post #854590
Posted Wednesday, January 27, 2010 11:23 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:25 AM
Points: 20,801, Visits: 32,725
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);




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)
Post #854598
Posted Wednesday, January 27, 2010 11:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:53 PM
Points: 5,366, Visits: 8,983
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
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
Post #854600
Posted Wednesday, January 27, 2010 11:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 20, 2014 8:32 PM
Points: 148, Visits: 645
You are so Awesome! Lynn. Thank very much.
Post #854602
Posted Wednesday, January 27, 2010 11:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 20, 2014 8:32 PM
Points: 148, Visits: 645
Thanks so much everyone!
Post #854603
Posted Wednesday, January 27, 2010 1:00 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:47 PM
Points: 35,555, Visits: 32,151
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #854683
Posted Wednesday, January 27, 2010 1:02 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:47 PM
Points: 35,555, Visits: 32,151
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #854686
Posted Wednesday, January 27, 2010 1:05 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:25 AM
Points: 20,801, Visits: 32,725
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

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)
Post #854687
Posted Wednesday, January 27, 2010 1:12 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:25 AM
Points: 20,801, Visits: 32,725
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);




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)
Post #854695
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse