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 12»»

Adding Seconds to Time field Expand / Collapse
Author
Message
Posted Wednesday, August 29, 2012 11:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 4, 2012 7:31 AM
Points: 2, Visits: 25
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.
Post #1351770
Posted Wednesday, August 29, 2012 11:07 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 6:43 AM
Points: 20,807, Visits: 32,743
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?



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 #1351774
Posted Wednesday, August 29, 2012 11:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 4, 2012 7:31 AM
Points: 2, Visits: 25
It is a time field
Post #1351776
Posted Wednesday, August 29, 2012 11:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:23 PM
Points: 12,927, Visits: 32,330
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.0000000 16:52:41.0000000
*/
declare @time time
SET @time = '16:40:27'
SELECT @time,DATEADD(ss,734,@time)



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1351778
Posted Wednesday, August 29, 2012 11:14 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 6:43 AM
Points: 20,807, Visits: 32,743
This:


declare @TimeStr time = '16:40:27',
@SecondsAtStatus int = 734;
select @TimeStr, dateadd(ss, @SecondsAtStatus, @TimeStr);





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 #1351780
Posted Thursday, August 30, 2012 11:29 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 35,606, Visits: 32,190
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."

(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 #1352393
Posted Thursday, August 30, 2012 11:53 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 6:43 AM
Points: 20,807, Visits: 32,743
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





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 #1352402
Posted Thursday, August 30, 2012 4:06 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 35,606, Visits: 32,190
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."

(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 #1352524
Posted Thursday, August 30, 2012 6:55 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, November 24, 2014 4:53 AM
Points: 3,422, Visits: 5,368
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1352547
Posted Thursday, August 30, 2012 8:33 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 8:51 PM
Points: 35,606, Visits: 32,190
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."

(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 #1352559
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse