? ? ? ? ? ? ? ? ? ? ? ? ? ? 計算兩個時間列工作日差值

需求描述

需求：對EMP表里員工KING和SMITH的hiredate入職時間差，這里單位是天且是工作日時間，即周末不計算在內.

解決方法:通過DATEDIFF函數來完成.

注: 數據庫數據集SQL腳本詳見如下鏈接地址

員工表結構和數據初始化SQL腳本

SQL代碼

-- SQL Serer： SELECT B.ename b_Ename,B.HIREDATE b_hiredate,A.ename a_Ename, A.HIREDATE a_hiredate ,SUM(CASE WHEN DATENAME(DW,DATEADD(DAY,seq.pos,A.HIREDATE)) IN ('星期六','星期日')THEN 0 ELSE 1 END) AS workday_Num FROM emp A,emp B,(SELECT number AS pos FROM master.[dbo].[spt_values] WHERE type = 'P') seq WHERE A.ename = 'SMITH' AND B.ename = 'KING' AND seq.pos <= DATEDIFF(DAY,A.HIREDATE,B.HIREDATE) GROUP BY B.ename ,B.HIREDATE,A.ename , A.HIREDATE

執行結果

?

-- Mysql： SELECT B.ename b_Ename,B.HIREDATE b_hiredate,A.ename a_Ename, A.HIREDATE a_hiredate, SUM(CASE WHEN DATE_FORMAT(DATE_ADD(A.hiredate,INTERVAL pos DAY),'%w') IN (0,6) THEN 0 ELSE 1 END) AS workday_Num FROM emp A,emp B,(SELECT i-1 AS pos FROM tb_incr) seq WHERE A.ename = 'SMITH' AND B.ename = 'KING' AND seq.pos <=DATEDIFF(B.HIREDATE,A.HIREDATE) GROUP BY B.ename ,B.HIREDATE,A.ename , A.HIREDATE

?

總結

以上是生活随笔為你收集整理的处理时间_3_计算两个时间列工作日差值的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。