Adding Seconds to Time field

Question

Adding Seconds to Time field

celayne

Newbie

Points: 6
More actions
August 29, 2012 at 11:03 am

#266039

I have a field that contains a time (i.e. 16:40:27) and I have a field of "SecondsAtStatus" (i.e. 734).
I need to add the "SecoundsAtStatus" to the Time field to come up with an End Time. Any help is appreciated.

Viewing 11 posts - 1 through 10 (of 10 total)

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

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

celayne (8/29/2012)
I have a field that contains a time (i.e. 16:40:27) and I have a field of "SecondsAtStatus" (i.e. 734).
I need to add the "SecoundsAtStatus" to the Time field to come up with an End Time. Any help is appreciated.

Is this an actual time field or is it defined as a varchar?

celayne Newbie Points: 6 More actions · Answer 2

celayne

Newbie

Points: 6

August 29, 2012 at 11:10 am

#1531527

It is a time field

Lowell SSC Guru Points: 323504 More actions · Answer 3

As Lynn was asking, it's a real TIME datatype, you can add values via dateadd.

/*

(No column name)(No column name)

16:40:27.000000016:52:41.0000000

*/

declare @time time

SET @time = '16:40:27'

SELECT @time,DATEADD(ss,734,@time)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

This:

declare @TimeStr time = '16:40:27',

@SecondsAtStatus int = 734;

select @TimeStr, dateadd(ss, @SecondsAtStatus, @TimeStr);

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 5

celayne (8/29/2012)
It is a time field

Then you actually have a potentially serious problem. What do you want to do when adding the seconds takes you over 24 hours???

--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 6

Jeff Moden (8/30/2012)
celayne (8/29/2012)
It is a time field
Then you actually have a potentially serious problem. What do you want to do when adding the seconds takes you over 24 hours???

Doesn't overflow, just rolls around.

declare @TimeStr time = '16:40:27',

@SecondsAtStatus int = 86399;

select @TimeStr, dateadd(ss, @SecondsAtStatus, @TimeStr)

go

declare @TimeStr time = '16:40:27',

@SecondsAtStatus int = 86400;

select @TimeStr, dateadd(ss, @SecondsAtStatus, @TimeStr)

go

declare @TimeStr time = '16:40:27',

@SecondsAtStatus int = 86401;

select @TimeStr, dateadd(ss, @SecondsAtStatus, @TimeStr)

go

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

Like I said... serious problem.

--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: 86908 More actions · Answer 8

Jeff Moden (8/30/2012)
Like I said... serious problem.

Jeff - I don't get it (no coffee yet this morning).

If it starts at 23:59:00 and ends at 00:01:00 (120 seconds), what is the problem?

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: 1003857 More actions · Answer 9

dwain.c (8/30/2012)
Jeff Moden (8/30/2012)
Like I said... serious problem.
Jeff - I don't get it (no coffee yet this morning).
If it starts at 23:59:00 and ends at 00:01:00 (120 seconds), what is the problem?

It depends... on whether or not that's the desired effect. If you're trying to aggregate time (for example) then the wrap at 24 hours is a killer on a TIME "field".

--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: 86908 More actions · Answer 10

Jeff Moden (8/30/2012)
dwain.c (8/30/2012)
Jeff Moden (8/30/2012)
Like I said... serious problem.
Jeff - I don't get it (no coffee yet this morning).
If it starts at 23:59:00 and ends at 00:01:00 (120 seconds), what is the problem?
It depends... on whether or not that's the desired effect. If you're trying to aggregate time (for example) then the wrap at 24 hours is a killer on a TIME "field".

So by aggregate a time, you mean to sum up the times as durations.

It was my impression that TIME datatypes should not be used this way, although undoubtedly some people do that.

So thanks for the warning.

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