计算Sql server 2005中两个日期之间的TotalHours [英] calculate TotalHours Between two dates in sql server 2005
问题描述
大家好,
i有两次StartTime和EndTime,我必须计算这两次的总小时数,总结一下总时数
请帮助
问候
Nirmala Saravanan
< blockquote> DATEDIFF [ ^ ]将为您完成工作。
SELECT DATEDIFF(小时,' 2012-12-31 12 :00:00.00',' 2013-02-10 13:00:00.0000000' );
将返回985,这是正确的。
假设一个虚构的表结构,因为你没有指定任何,这个声明将总结总小时数:
选择 sum(DATEDIFF(小时,DateFiled1,DateField2))来自 TableName
[根据新要求更新]
要获得hh:mm格式,您需要声明一个函数,或者在查询中使用以下方法:
DECLARE @ d1 DATETIME
DECLARE @ d2 DATETIME
SET @ d1 = ' 2013-01-01 00:00'
< span class =code-keyword> SET @ d2 = ' 2013-01-02 10:25'
SELECT DATEDIFF(minut) e, @ d1 , @ d2 )
SELECT CAST((DATEDIFF(分钟, @ d1 , @ d2 )/ 60 ) AS VARCHAR ( 8 ))+ ' :' +
right (' 0' + CAST ((DATEDIFF(分钟, @ d1 , @ d2 )% 60 ) AS VARCHAR ( 2 )), 2 )
当然,最后一个陈述是关键,其余的是用于演示。
>
hi all,
i have a two times StartTime and EndTime and i have to calculate total hours for these two time and i have sum up that total hours
Pls Help
Regards
Nirmala Saravanan
DATEDIFF[^] will do the job for you.
SELECT DATEDIFF(hour, '2012-12-31 12:00:00.00', '2013-02-10 13:00:00.0000000');will return 985, which is correct.
So supposing a fictive table structure since you have not specified any, this statement will summarize what the total hours:
select sum(DATEDIFF(hour, DateFiled1, DateField2)) from TableName
[Update based on new requirement]
To have a hh:mm format, you need to declare a function, or simply use following approach in your query:
DECLARE @d1 DATETIME DECLARE @d2 DATETIME SET @d1 = '2013-01-01 00:00' SET @d2 = '2013-01-02 10:25' SELECT DATEDIFF(minute, @d1, @d2) SELECT CAST((DATEDIFF(minute, @d1, @d2) / 60) AS VARCHAR(8)) + ':' + right('0' + CAST((DATEDIFF(minute, @d1, @d2) % 60) AS VARCHAR(2)),2)
Of course, the last statement is the key, the rest is for demonstration.
这篇关于计算Sql server 2005中两个日期之间的TotalHours的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!