MSSQL - Extract Second Time
I am new to Microsoft SQL Server. And I wanted to extract the second from the temporary datatype.
I have experience with PostgreSQL and am retrieving the second use case for this feature.
EXTRACT(EPOCH FROM timestamp)
But in Microsoft SQL Server I found this function -
DATEDIFF(second, 0, timestamp)
Which gives me the second if the time is less than 24 hours.
But when I try a query like this.
SELECT SUM(DATEDIFF(second, '0:00:00', '86:01:12'))
This results in an error.
Conversion failed when converting date and/or time from character string
Searching for a solution since Saturday and haven't found one.
Someone can tell me how I can convert a time datatype that is greater than 24 hours per second.
Thanks in advance.
source to share
This may not be the best way, but you can blow it up in hours, minutes and seconds and then add them all. If I understand correctly, what you want to do is convert the time format to seconds.
This works in 2008 R2
DECLARE @inputTime AS VARCHAR(10) = '86:01:12'
DECLARE @timeSec AS INT = SUBSTRING(@inputTime,1,2) * 3600 + SUBSTRING(@inputTime,4,2) * 60 + SUBSTRING(@inputTime,7,2)
PRINT @timeSec
You can do it as a one-liner to nest it in any of your queries:
SELECT SUBSTRING('86:01:12',1,2) * 3600 + SUBSTRING('86:01:12',4,2) * 60 + SUBSTRING('86:01:12',7,2)
As you can see, it will treat it as a string, get the first two characters as hours, multiply by 3600 (60 seconds per minute * 60 minutes per hour). Then follow the minutes and multiply by 60 (60 seconds per minute), and for the last time add seconds up, which is * 1, since that's the resolution you're looking for.
For ease of use, you can create a function and use it to make your code less messy:
CREATE FUNCTION dbo.stampSeconds (@inputTime VARCHAR(10))
RETURNS INT
AS BEGIN
DECLARE @timeSec AS INT = SUBSTRING(@inputTime,1,2) * 3600 + SUBSTRING(@inputTime,4,2) * 60 + SUBSTRING(@inputTime,7,2)
RETURN @timeSec
END
Which you can use like this:
SELECT dbo.stampSeconds ('29:07:01')
source to share