系統有一個請假表,記錄為工號,請假日期,開始時間與結束時間,(每工號每天請假有一條記錄),
每天的上班時間,早上9:00--13:00,14:00--18:00,星期六只上半天(即9:00--13:00),
現要把每天的時間轉化為小時或分鐘,不知能否用一條詞句實現,而不用游標?先謝謝了!
記錄例如:
empId leaveDate inTime outTime
008 2006-06-19 00:00:00.000 09:00:00 18:00:00
008 2006-06-20 00:00:00.000 09:00:00 13:00:00
008 2006-07-19 00:00:00.000 14:00:00 18:00:00
每天的上班時間,早上9:00--13:00,14:00--18:00,星期六只上半天(即9:00--13:00),
現要把每天的時間轉化為小時或分鐘,不知能否用一條詞句實現,而不用游標?先謝謝了!
記錄例如:
empId leaveDate inTime outTime
008 2006-06-19 00:00:00.000 09:00:00 18:00:00
008 2006-06-20 00:00:00.000 09:00:00 13:00:00
008 2006-07-19 00:00:00.000 14:00:00 18:00:00
intime varchar(100),outtime varchar(100))insert into tb select '008',' 2006-06-19 00:00:00.000','09:00:00 ','18:00:00' union allselect '008',' 2006-06-20 00:00:00.000','09:00:00 ','13:00:00' union allselect '008',' 2006-07-19 00:00:00.000','14:00:00 ','18:00:00'
SELECT empid, leavedate, intime, outtime, DATEDIFF(hh, intime, outtime) AS 小时,
DATEDIFF(n, intime, outtime) AS 分钟
FROM tb
因不不知道你intime 和outtime的类型.
所以告诉你方法:用datediff可以实现..
empId leaveDate inTime outTime 小時
008 2006-06-19 00:00:00.000 09:00:00 18:00:00 8.0
008 2006-06-20 00:00:00.000 09:00:00 13:00:00 4.0
008 2006-07-19 00:00:00.000 14:00:00 18:00:00 4.0
> 4 THEN DATEDIFF(hh, intime, outtime) - 1 ELSE DATEDIFF(hh, intime, outtime)
END AS 小时, DATEDIFF(n, intime, outtime) AS 分钟
FROM tb
> 4 THEN DATEDIFF(hh, intime, outtime) - 1 ELSE DATEDIFF(hh, intime, outtime)
END AS 小时, CASE WHEN DATEDIFF(hh, intime, outtime) > 4 THEN (DATEDIFF(n,
intime, outtime) - 60) ELSE DATEDIFF(n, intime, outtime) END AS 分钟
FROM tb1
SELECT empid, leavedate, intime, outtime,
(DATEDIFF(hh, intime, outtime)-case when datediff(hh,'13:00:00',outtime) > 0 and datediff(hh,intime,'14:00:00') > 0 then 1 else 0 end) AS 小时
FROM tb