将毫秒转换为天,小时,分钟和秒 [英] Converting Milliseconds to Days, hours, minutes and seconds
问题描述
我在Microsoft SQL Server 2008R2中有一个bigint字段,其中充满了刻度线(一个刻度线代表一百纳秒或一千万分之一秒.一毫秒内有10,000个刻度).
i have a bigint field in Microsoft SQL Server 2008R2 filled with ticks (A single tick represents one hundred nanoseconds or one ten-millionth of a second. There are 10,000 ticks in a millisecond.)
http://msdn.microsoft.com/zh-我们/library/system.datetime.ticks.aspx
,我需要将所有记录的总和转换为Days:Hours:Minutes:Seconds:Milliseconds.
and i need to convert the sum of all records to Days:Hours:Minutes:Seconds:Milliseconds.
它适用于单个记录:
SELECT CONVERT(TIME, DATEADD(ms, duration/10000, 0)) FROM tblMediaFileProperties WHERE FileId = '6C0A849D-95B4-4755-A923-B9DD8F1AF23E'
,但如果使用以下方法将其总计为所有记录:
but if a sum it up to all records using:
SELECT CONVERT(TIME, DATEADD(ms, SUM(duration/10000), 0)) FROM tblMediaFileProperties
我得到一个:
将表达式转换为数据类型int的算术溢出错误.
Arithmetic overflow error converting expression to data type int.
我知道溢出来自于转换为数据类型TIME函数...
i know the overflow comes from the CONVERT to Data Type TIME Function...
帮助表示感谢,谢谢!
推荐答案
It's too big for DATEADD which only accepts an int
.
Break it into two parts: seconds, then milliseconds.
SELECT CONVERT(TIME,
DATEADD(ms, SUM(duration/10000 % 1000),
DATEADD(ss, SUM(duration/10000000), 0)))
FROM tblMediaFileProperties
如果您的总时长超过1天,则可以使用它分别获取天数和hr:min:sec:ms.如果您实际上想要文本形式的结果,则是强制转换和字符串连接的问题.
And if your total duration goes above 1 day, you can use this to get the days and hr:min:sec:ms separately. It's a matter of cast and string concat if you actually want the result in textual form.
declare @duration bigint
set @duration = 1230000000
SELECT @duration/10000/1000/60/60/24 DAYS,
CONVERT(TIME,
DATEADD(ms, SUM(@duration/10000 % 1000),
DATEADD(ss, SUM(@duration/10000000), 0))) HR_MIN_SEC
这篇关于将毫秒转换为天,小时,分钟和秒的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!