SQL Clone
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
Old Hand
Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)

Group: General Forum Members
Points: 342 Visits: 712
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
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3837 Visits: 4646
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-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40752 Visits: 38567
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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10146 Visits: 10575
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
Old Hand
Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)

Group: General Forum Members
Points: 342 Visits: 712
You are so Awesome! Lynn. Thank very much.
tan110
tan110
Old Hand
Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)Old Hand (342 reputation)

Group: General Forum Members
Points: 342 Visits: 712
Thanks so much everyone!
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89315 Visits: 41143
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89315 Visits: 41143
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

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

Group: General Forum Members
Points: 40752 Visits: 38567
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-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40752 Visits: 38567
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