Question

SQL语句如何统计a时间和b时间有多少个工作日？

假设只有周六和周日为节假日

Answer 1

首先保证,a 与 b 表的时间是 date 或者 datetime 类型：
1、Oracle 写法：

select sum (case when to_char(a.time,'d')-1>=1 and to_char(a.time,'d')-1<=5 then 1
else 0
end)
from tablename a;
2.MSSQL 和 Sybase 写法：

select sum(case when datepart(weekday,a.datetime)-1 >=1 and datepart(weekday,a.datetime)-1<=5 then 1
else 0
end)
from tablename a

Answer 2

declare @a datetime
declare @b datetime
declare @c int
set @a = '2012-07-01'
set @b = GETDATE()
if(DATEPART(WEEKDAY, @a)=1 or DATEPART(WEEKDAY, @a)=7)
set @c = DATEDIFF(DAY, @a, @b)/7 * 5
else
set @c = (DATEDIFF(DAY, @a, @b)/7 * 5) + DATEDIFF(DAY, @a, @b) % 7 - 2

select @c
这样就OK

Answer 3

declare @a datetime
declare @b datetime
declare @c int
set @a = '2012-07-01'
set @b = GETDATE()
if(DATEPART(WEEKDAY， @a)=1 or DATEPART(WEEKDAY， @a)=7)
set @c = DATEDIFF(DAY， @a， @b)/7 * 5
else
set @c = (DATEDIFF(DAY， @a， @b)/7 * 5) + DATEDIFF(DAY， @a， @b) % 7 - 2
。