how to get seconds from my time ....

Question

Post reply

how to get seconds from my time ....

dastagiri16

Hall of Fame

Points: 3544
More actions
December 29, 2011 at 9:53 pm

#252795

hi,
@time ='20:10:10'
how can i get total second from the time value is there any function in sql 2005 or 2008...
Thanks,
Giri.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Paul White SSC Guru Points: 150457 More actions · Answer 1

DECLARE @time TIME = '20:10:10'

SELECT total_seconds =

DATEPART(SECOND, @time) +

60 * DATEPART(MINUTE, @time) +

3600 * DATEPART(HOUR, @time)

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

VIG SSC-Addicted Points: 488 More actions · Answer 2

DECLARE @time TIME = '20:10:10'

select total_seconds =DATEDIFF(second,0,cast(@time as datetime))

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

VIG (12/30/2011)
DECLARE @time TIME = '20:10:10'
select total_seconds =DATEDIFF(second,0,cast(@time as datetime))

😉

--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)
Intro to Tally Tables and Functions

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

VIG (12/30/2011)
DECLARE @time TIME = '20:10:10'
select total_seconds =DATEDIFF(second,0,cast(@time as datetime))

The time string can be directly assigned to a datetime variable as 1900-01-01 20:10:10:

DECLARE @time datetime = '20:10:10'

select total_seconds =DATEDIFF(second,0,@time)

And the cast of the string to a date can be implicit in the DATEDIFF function:

select total_seconds =DATEDIFF(second,0,'20:10:10')

Paul White SSC Guru Points: 150457 More actions · Answer 5

All very good. And yes, a single DATEDIFF is 'better' than three DATEPARTs.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

sami.sqldba Ten Centuries Points: 1248 More actions · Answer 6

SELECT DATEDIFF(SECOND,0,'20:10:10')

Thanks & Regards
Syed Sami Ur Rehman
SQL-Server (Developer)
Hyderabad
Email-sami.sqldba@gmail.com

clubbavich Default port Points: 1449 More actions · Answer 7

Keep in mind that the TIME datatype is not available in SQL Server 2005, only 2008 and newer. I realize that this is the 2008 forum, but you did ask for a function in 2005 or 2008.

For best compatibility, I would recommend the following:

DECLARE @baseline datetime, @testvalue datetime

SET @baseline = CONVERT(datetime, '1900-01-01T00:00:00', 126)

SET @testvalue= CONVERT(datetime, '2012-01-03T12:30:45', 126)

SELECT CAST(DATEDIFF(Minute, @baseline, @testvalue) AS bigint) * 60 + DATEPART(second, @testvalue)

--Or, depending on the baseline you want, you can simply do

SELECT DATEDIFF(second, @baseline, @testvalue)

--Caution, Too great a difference between your baseline and your value could cause an integer overflow

Rather than implicitly convert the baseline value to a datetime, I explicitly converted it with a specified style format. The reason for doing this is that any view/computed column/TVF will be non-deterministic if it includes an implicit datetime conversion like that. Different SQL servers have different default date formats. As a result, SQL cannot guarantee that the implicit conversion will always end up with the same result. Converting the string to a datetime with a specific file format takes care of that problem.

The biggest disadvantage of non-deterministic methods is that they limit what can be indexed. You may not need to index these particular results, but if you want to reuse this code, you may not want to limit your options.