sql server - MSSQL - Extract second from time -

sql server - MSSQL - Extract second from time -

i'm new microsoft sql server. , wanted extract sec time datatype.

i've got experience in postgresql , extract sec using function.

extract(epoch timestamp)

but in microsoft sql server, found function -

datediff(second, 0, timestamp)

which gives me sec if time less 24 hours.

but when seek query this.

select sum(datediff(second, '0:00:00', '86:01:12'))

it gives me error that.

conversion failed when converting date and/or time character string

searching solution saturday , couldn't find it.

some 1 help me out, how can convert time datatype greater 24 hours seconds.

thanks in advance.

maybe not best way, can explode hours, minutes , seconds, add together them up. if understand right wish convert time format seconds.

this works on 2008 r2

declare @inputtime varchar(10) = '86:01:12' declare @timesec int = substring(@inputtime,1,2) * 3600 + substring(@inputtime,4,2) * 60 + substring(@inputtime,7,2) print @timesec

you can one-liner, nest in of queries:

select substring('86:01:12',1,2) * 3600 + substring('86:01:12',4,2) * 60 + substring('86:01:12',7,2)

as can see treat string, first 2 chars hours, multiply 3600 (60 seconds in min * 60 minutes in hour). next take minutes , multiply them 60 (60 seconds in minute), , lastly add together seconds up, * 1 since resolution looking for.

for easier use, can create function, , utilize onwards create code less messy:

create function dbo.stampseconds (@inputtime varchar(10)) returns int begin declare @timesec int = substring(@inputtime,1,2) * 3600 + substring(@inputtime,4,2) * 60 + substring(@inputtime,7,2) homecoming @timesec end

which can utilize like:

select dbo.stampseconds ('29:07:01')

sql-server database