怎样计算两个日期天数差(去掉周六日)??
------解决方案--------------------
DATEDIFF(d,d1,d2)-2*(DATEDIFF(w,d1,d2)
思路是这样的,但肯定要许多判断,该写成一个函数
------解决方案--------------------
关键是要处理d1,d2本身是周六日的情况
------解决方案--------------------
这贴子比较有意思的,所以我写了一个
这个是比较10.1号距离现在有多少天(去掉周六日)
- SQL code
declare @dateBegin datetime, @dateEnd datetime, @dateTmp datetimedeclare @day int, @i int, @j intselect @dateBegin = '20071001', @dateEnd = getDate()select @day = datediff(dd,@dateBegin,@dateEnd)select @i = 1, @j = 1while (@i <= @day)begin select @dateTmp = dateadd(dd,@i,@dateBegin) if (datePart(weekday,@dateTmp) > 1 and datePart(weekday,@dateTmp) < 7) select @j = @j + 1 select @i = @i + 1endselect @j '两个日期差距的天数'
------解决方案--------------------
按 hb_gx 的方法 写成一个函数
------解决方案--------------------
算法可以这样
1. 先求出差几天
2. 再除以 7 算出是 N = 几周, -> 2N
3. 再判断 被7整除的余数有几天是周六,周日, m
4. select 2N + m
---------
这样快一点
------解决方案--------------------
- SQL code
declare @k table(datestr datetime)insert into @k select '2007-10-01'union all select '2007-10-02'union all select '2007-10-03'union all select '2007-10-04'union all select '2007-10-05'union all select '2007-10-06'union all select '2007-10-07'union all select '2007-10-08'union all select '2007-10-09'union all select '2007-10-10'union all select '2007-10-11'union all select '2007-10-12'union all select '2007-10-13'union all select '2007-10-14'select * from @kselect count(1) from @k where convert(varchar(10),datestr,120) between '2007-10-01' and '2007-10-14' and datepart(dw,datestr)<>7