日期:2014-05-18 浏览次数:20654 次
declare @date1 datetime,@date2 datetime set @date1='2011-05-12' set @date2='2011-05-15' select 'yes' where datediff(d,@date1,@date2)<3+(case when datepart(dw,@date1)>datepart(dw,@date2) then 1 else 0 end) /* ---- yes (1 行受影响) */
------解决方案--------------------
/* ********************************************************************************* Program ID: FUN_GetDaysOfWeeks Purpose : 求某段时间内某一星期日期的天数 Author : Jesse Date : 2011.02.19 ********************************************************************************* */ Create Function dbo.FUN_GetDaysOfWeeks( @DateS smalldatetime, @DateE smalldatetime, @WeekValue int) --星期的某一天:1-周日、2-周一...7-周六 Returns int As Begin declare @nResult int, --结果天数 @nWeekValue int, --起始日期是星期几(1-周日、2-周一...7-周六) @nDays int, --时间段内的天数 @nBeforeDays int, --起始日期到第一次相符日期的天数 @sWeekName varchar(20), --所求日期的星期名称 @tmpDate smalldatetime set @WeekValue=@WeekValue % 7 if @WeekValue=0 set @WeekValue=7 --如果起始日期大于终止日期,则对换 if @DateS>@DateE select @tmpDate=@DateE,@DateE=@DateS,@DateS=@tmpDate --取得起始日期的星期数,与设置无关(1-周日、2-周一...7-周六) select @nWeekValue=(Datepart(dw,@DateS)+(@@Datefirst %7))%7 if @nWeekValue=0 set @nWeekValue=7 set @sWeekName=DateName(dw,DateAdd(d,@WeekValue-@nWeekValue,@DateS)) if @WeekValue>=@nWeekValue set @nBeforeDays=@WeekValue-@nWeekValue else set @nBeforeDays=7-abs(@WeekValue-@nWeekValue) --取得时间段内的天数 select @nDays=Datediff(d,@DateS,@DateE)+1 if @nDays<@nBeforeDays select @nResult=0 else select @nResult=ceiling((@nDays-@nBeforeDays)/7.0) Return @nResult end /* --select @@datefirst select dbo.FUN_GetDaysOfWeeks('2011-03-01','2011-05-31',7) */ --先建一函数,再利用函数计算得出 select * from t where datediff(d,date1,date2)- dbo.FUN_GetDaysOfWeeks('2011-03-01','2011-05-31',7)<=3
------解决方案--------------------
select (case when date2-date1>4 then 0 else when datename(dw,date1) <3 then 0 else then 1) as Complete from tb
------解决方案--------------------
这个是需要写函数来判断时间在所在区间内且是不是星期六
------解决方案--------------------
楼主需求不太明确,猜测如下:
create table table1(date1 datetime,date2 datetime) insert into table1 select '2011-5-9','2011-5-10' insert into table1 select '2011-5-9','2011-5-15' insert into table1 select '2011-5-13','2011-5-10' insert into table1 select '2011-5-12','2011-5-15' go select date1,date2,'满足要求' from table1 where datediff(d,date1,date2)<3+(case when datepart(dw,date1)>datepart(dw,date2) then 1 else 0 end) go drop table table1 /* date1 date2 ----------------------- ----------------------- -------- 2011-05-09 00:00:00.000 2011-05-10 00:00:00.000 满足要求 2011-05-13 00:00:00.000 2011-05-10 00:00:00.000 满足要求 2011-05-12 00:00:00.000 2011-05-15 00:00:00.000 满足要求 (3 行受影响) */