日期时间差铸造

Question

DateTime1：2010-11-23 15:30:00.000

DATETIME2：2010-11-23 18:30:00.000

DATETIME2 - DateTime1应该03:00:00

但是当我使用下面的查询

SELECT (EndTime - StartTime) as Hour From [Day] 

返回

1900-01-01 03:00:00.000 

我怎样才能得到它像03:00？

Answer 1

Datediff函数适用于此操作。

SELECT DATEDIFF (hour , StartTime, EndTime) as hour From [Day] 

，看一看http://msdn.microsoft.com/en-us/library/ms189794.aspx

Answer 2

正如你所说，DATEDIFF是合适的。格式可能是最好留给客户端，但下面将按照您的要求工作

DECLARE @Start DATETIME = '20120716 09:00', 
     @End DATETIME = '20120717 12:00' 

SELECT CASE WHEN DATEDIFF(HOUR, @Start, @End) < 10 THEN '0' ELSE '' END + CAST(DATEDIFF(HOUR, @Start, @End) AS VARCHAR) + ':' + 
     RIGHT('0' + CAST(DATEDIFF(MINUTE, @Start, @End) - (DATEDIFF(HOUR, @Start, @End) * 60) AS VARCHAR), 2) [Difference]