问题描述
大家好，
i有开始时间和结束时间，
如何计算这个在SQL SERVER 2008中。
我必须采用什么数据类型？
仅限小时和分钟。
任何人都可以帮助我
Hi guys,
i have Starting time and ending time,
how can in calculate this in SQL SERVER 2008.
what datatype i have to take?
Only in Hours & Minutes.
Can anyone plz help me
推荐答案
SELECT DATEDIFF(mi, CAST('6:30' AS DATETIME), CAST('14:00' AS DATETIME)) / 60 AS Hours,
DATEDIFF(mi, CAST('6:30' AS DATETIME), CAST('14:00' AS DATETIME)) % 60 AS Minutes
x hours y minutes = (x*60)+y
示例：
Example:
2 hours 10 minutes (02:10) = (2*60)+10 = 130
在退休时你可以得到结果
and while retiving you can get the result as
SELECT CAST((t/60) AS VARCHAR)+':'+CAST((130%60) AS VARCHAR) AS Result
要正确格式化，
To format correctlly,
SELECT RIGHT('0'+CAST((t/60) AS VARCHAR),2)+':'+CAST((t%60) AS VARCHAR) AS Result
SELECT RIGHT('0'+CAST((@EndingTime-@StartingTime)/60 AS VARCHAR),2)+':'+CAST((@EndingTime-@StartingTime)%60 AS VARCHAR) AS Result
您可能希望使用 TIME 数据类型，但它也将保持占秒数。在您的情况下，您可以将零作为秒的值传递。
请参阅此链接： []
并且在显示数据时，您可以使用 -
You may want to use TIME datatype, but it will keep account for seconds also. In your case you can pass Zero as value for seconds.
Refer this link: time (Transact-SQL)[^]
and while showing data, you can use-
SELECT SUBSTRING( CONVERT(VARCHAR, YourField,108),1,5)
SELECT RIGHT('0'+CAST(DATEDIFF(MINUTE,@StartingTime,@EndingTime)/60 AS VARCHAR),2)+':'+CAST(DATEDIFF(MINUTE,@StartingTime,@EndingTime)%60 AS VARCHAR) AS RESULT
希望，它有助于：）
Hope, it helps :)
这篇关于如何在SQL SERVER 2008中只计算时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！