DECLARE @sec int
DECLARE @tH int
DECLARE @tM int
DECLARE @tS int
SET @sec = 92730
SET @tH = @sec / 3600
SET @tM = @sec / 60 - @tH * 60
SET @tS = @sec - (@tH * 3600 + @tM * 60)
SELECT CONVERT(NVARCHAR(10), CONVERT(NVARCHAR(5), @tH) + ':' + CONVERT(NVARCHAR(5), @tM) + ':' + CONVERT(NVARCHAR(5), @tS)) AS [Total Time]
RESULT for 92730 = 25:45:30
You should write a
function[
^] or
stored procedure[
^] to convert seconds into time format.
[EDIT]
As i wrote above you should write custom function...
Copy code below, paste into MS SQL Sercere Manager -> New Query, edit and execute in your database.
This will add UserDefinedFunction [dbo].[GetTimeFromSeconds]
USE [YourDataBaseName]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GetTimeFromSeconds]
(
@sec int
)
RETURNS nvarchar(10)
AS
BEGIN
DECLARE @tH int
DECLARE @tM int
DECLARE @tS int
DECLARE @Result nvarchar(10)
SET @tH = @sec / 3600
SET @tM = @sec / 60 - @tH * 60
SET @tS = @sec - (@tH * 3600 + @tM * 60)
SELECT @Result = CONVERT(NVARCHAR(10), CONVERT(NVARCHAR(5), @tH) + ':' + CONVERT(NVARCHAR(5), @tM) + ':' + CONVERT(NVARCHAR(5), @tS))
RETURN @Result
END
Usage:
SELECT T.[Seconds], YourDataBaseName.[dbo].[GetTimeFromSeconds] (T.[Seconds]) AS [Total Time]
FROM YourTableName AS T
[/EDIT]