DATEDIFF(datepart, startdate, enddate)
datepart 可為:year, quarter, month, dayofyear, day, week, hour, minute, second, millisecond 等。
| 需求 | 語句 |
|---|---|
| 相差天數 | SELECT DATEDIFF(day, '2008-08-25', '2008-09-01') |
| 相差秒數 | SELECT DATEDIFF(second, '2009-08-25 12:15:12', '2009-09-01 07:18:20') |
| 相差分鐘數 | SELECT DATEDIFF(minute, '2009-08-25 12:15:12', '2009-09-01 07:18:20') |
| 相差小時數 | SELECT DATEDIFF(hour, '2009-08-25 12:15:12', '2009-09-01 07:18:20') |
DECLARE @dt1 DATETIME = '2008-08-04 09:36:41',
@dt2 DATETIME = '2008-08-02 09:33:39';
DECLARE @seconds INT = DATEDIFF(second, @dt2, @dt1);
DECLARE @days INT = @seconds / 86400;
SET @seconds = @seconds % 86400;
DECLARE @hours INT = @seconds / 3600;
SET @seconds = @seconds % 3600;
DECLARE @minutes INT = @seconds / 60;
SET @seconds = @seconds % 60;
SELECT CAST(@days AS VARCHAR) + '天' +
CAST(@hours AS VARCHAR) + '小時' +
CAST(@minutes AS VARCHAR) + '分' +
CAST(@seconds AS VARCHAR) + '秒';
利用 DATEADD 與 DATEDIFF 組合:
DECLARE @t TABLE (beginday INT, begintime VARCHAR(20), endday INT, endtime VARCHAR(20));
INSERT @t VALUES (1,'16:00',2,'12:20'), (1,'03:00',3,'19:10');
SELECT RTRIM(datediff_minute/60) + ':' + RTRIM(datediff_minute%60) AS duration
FROM (
SELECT DATEDIFF(minute,
DATEADD(day, beginday, CAST(begintime AS DATETIME)),
DATEADD(day, endday, CAST(endtime AS DATETIME))
) AS datediff_minute
FROM @t
) t;
TRUNC(SYSDATE) - TO_DATE('2009-01-04','YYYY-MM-DD') 直接相減得天數。AGE(end, start) 回傳 interval,可用 EXTRACT 取各部份。