在 sql server 2005 中将秒转换为分钟和秒的有效方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2316288/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Efficient way to convert second to minute and seconds in sql server 2005
提问by priyanka.bangalore
Suppose I have 90 seconds. If I want to display the result in terms of minutes and second, I do it by using
假设我有 90 秒。如果我想以分钟和秒为单位显示结果，我可以使用
select Time= '0' + CAST( 90/60 as varchar(2)) + ':' + CAST( 90%60 as varchar(2))
The output is
输出是
Time
01:30
时间
01:30
I have appended 0(zero) because if you do a select getdate()the output will be
我附加了 0（零），因为如果你做一个select getdate()输出将是
yyyy-mm-dd hh:mm:ss:ms
yyyy-mm-dd hh:mm:ss:ms
What is the standard way and recommended practice to do such a conversion?
进行这种转换的标准方法和推荐做法是什么？
Thanks
谢谢
回答by Carlos Gutiérrez
With hours:
小时数：
SELECT CONVERT(CHAR(8),DATEADD(second,90,0),108)
00:01:30
Ignoring hours:
忽略时间：
SELECT RIGHT(CONVERT(CHAR(8),DATEADD(second,90,0),108),5)
01:30
回答by Sandeep Pulikonda
Try this:
尝试这个：
select convert(varchar(10), dateadd(second, 15794, 0), 108)
回答by Aaronaught
One of the first things I do on a fresh SQL database is add a Timespanfunction similar to this one (although I tend to include days and milliseconds as well):
我在新的 SQL 数据库上做的第一件事是添加一个Timespan类似于这个的函数（尽管我也倾向于包括天和毫秒）：
CREATE FUNCTION dbo.TimeSpan
(
@Hours int,
@Minutes int,
@Seconds int
)
RETURNS datetime
AS BEGIN
RETURN DATEADD(SS, @Hours * 3600 + @Minutes * 60 + @Seconds, 0)
END
Then you can format this however you want:
然后，您可以根据需要对其进行格式化：
SELECT SUBSTRING(CONVERT(char(8), dbo.TimeSpan(0, 0, 90), 108), 4, 5)
It might look more complicated at first, but the ability to reuse the TimeSpanfunction comes in very handy over time. For me it feels like a hack to always be writing DATEADDcalls against 0or '1753-01-01'.
起初它可能看起来更复杂，但TimeSpan随着时间的推移，重用该函数的能力会变得非常方便。对我来说，总是DATEADD针对0或编写调用感觉就像一个黑客'1753-01-01'。
