日期:2014-05-17 浏览次数:20650 次
select convert(decimal(15,2),datediff(n,'2011-07-28 17:30:00.000','2011-07-28 20:00:00.000'))/60
USE test
GO
-->生成表tb
if object_id('tb') is not null
drop table tb
Go
Create table tb([StartTime] datetime,[EndTime] datetime)
Insert into tb
Select '2011-07-28 17:30:00.000','2011-07-28 20:00:00.000'
Union all Select '2011-07-28 8:00:00.000','2011-07-29 17:00:00.000'
Union all Select '2011-07-28 8:00:00.000','2011-07-31 12:00:00.000'
Union all Select '2011-07-28 8:00:00.000','2011-07-29 12:00:00.000'
IF object_id('tempdb..#')IS NOT NULL
DROP TABLE #
SELECT
*
,CASE
WHEN DATEDIFF(hh,StartTime,EndTime)<=8
THEN EndTime-StartTime
WHEN DATEDIFF(hh,StartTime,EndTime)<=24
THEN '1900-01-01 08:00:00'
ELSE DATEADD(hh,DATEDIFF(dd,StartTime,EndTime)*8,'1900-01-01')
+CASE
WHEN DATEADD(dd,-1*DATEDIFF(dd,StartTime,EndTime),EndTime)-StartTime>'1900-01-01 8:00:00'
THEN '1900-01-01 8:00:00'
ELSE DATEADD(dd,-1*DATEDIFF(dd,StartTime,EndTime),EndTime)-StartTime
END
END AS Result
INTO #
FROM tb
SELECT
StartTime
,EndTime
,DATEDIFF(dd,'1900-01-01',Result) AS 天數
,CONVERT(VARCHAR(8),Result,108) AS [時間(時:分:秒)]
FROM #
/*
StartTime EndTime 天數 時間(時:分:秒)
----------------------- ----------------------- ----------- ---------
2011-07-28 17:30:00.000 2011-07-28 20:00:00.000 0 02:30:00
2011-07-28 08:00:00.000 2011-07-29 17:00:00.000 0 16:00:00
2011-07-28 08:00:00.000 2011-07-31 12:00:00.000 1 04:00:00