在SQL中将秒(Integer)显示为hh:mm:ss [英] Display Seconds(Integer) As hh:mm:ss in SQL

问题描述

你好团队，

我想在SQL Server中以hh:mm:ss格式显示Seconds(Integer).
我的表中有秒列.
例如我的秒数= 92730应该显示为25:45:30

如何在SQL查询中执行此操作
我在下面的查询中可以在Access中使用，但是如何在SQL中执行此操作-查询

Hello Team,

I want to display Seconds(Integer) as hh:mm:ss format in SQL Server.
I have seconds column in my table.
e.g. I have Seconds=92730 should be displayed as 25:45:30

How to do this in SQL Query
I have below query to use in Access but how to do this in SQL - Query

(Format(Int(Sum([Seconds])/3600),''00'') & '':'' & Format(Sum([Seconds]/86400),''nn:ss'')) AS [Total Time]

推荐答案

和一些不同的方法.

And a bit different approach.

DECLARE @RefDate DATETIME = CONVERT(datetime, '01.01.1900',104);
DECLARE @Seconds int = 92730;
DECLARE @CompareDate datetime = DATEADD(second, 92730, @RefDate);

SELECT @Seconds,
       CAST(DATEDIFF(HOUR, @RefDate, @CompareDate) AS varchar(100))
       + ':'
       + CAST(DATEPART(MINUTE, @CompareDate) AS varchar(2))
       + ':'
       + CAST(DATEPART(SECOND, @CompareDate) AS varchar(2))

因此，如果您定义参考日期，则可以使用DATEDIFF计算小时，然后从将秒添加到参考日期的日期中提取分钟和秒.

So if you define a reference date you can use the DATEDIFF to calculate the hours and then extract the minutes and seconds from the date where seconds are added to the reference date.

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]

92730 = 25:45:30的结果

您应该编写一个功能 [存储过程 [ ^ ]将秒转换为时间格式.


正如我上面所写，您应该编写自定义函数...

复制下面的代码，粘贴到MS SQL Sercere Manager中->在您的数据库中进行新的查询，编辑和执行.
这将添加UserDefinedFunction [dbo].[GetTimeFromSeconds]

RESULT for 92730 = 25:45:30

You should write a function[^] or stored procedure[^] to convert seconds into time format.


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
/****** Object:  UserDefinedFunction [dbo].[GetTimeFromSeconds]    Script Date: 05/09/2012 18:26:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GetTimeFromSeconds] 
(
	-- Add the parameters for the function here
	@sec int
)
RETURNS nvarchar(10)
AS
BEGIN
	-- Declare the return variable here
	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) 

	-- Add the T-SQL statements to compute the return value here
	SELECT @Result = CONVERT(NVARCHAR(10),  CONVERT(NVARCHAR(5), @tH) + ':' +  CONVERT(NVARCHAR(5), @tM) + ':' + CONVERT(NVARCHAR(5), @tS)) 

	-- Return the result of the function
	RETURN @Result

END

用法:

Usage:

SELECT  T.[Seconds], YourDataBaseName.[dbo].[GetTimeFromSeconds] (T.[Seconds]) AS [Total Time]
FROM YourTableName AS T

[/EDIT]

[/EDIT]

尝试类似的方法:

try something like this:

DECLARE @seconds int;
SET @seconds = 14400;

SELECT DATEADD(second, @seconds, '20120509')
,CONVERT(char(8), DATEADD(second, @seconds, '20120509'), 108);

这篇关于在SQL中将秒(Integer)显示为hh:mm:ss的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！