找出非节假日与周末的日期

比如有一个采购交期,要求不在假日交货. 我建了一个公司假日表,里面包含春节/国庆及公司规定的其他假期,但周末的日期没有输入在表里. 要求, 如果计算得出的交货日期在假期(含周末)里, 则交期提前到假期的前一个工作日. 要求查询不用游标/循环语句等. 直接用一句SQL查询,能做到吗? 表结构参考: V(Dt smalldatetime) 假期表 D(Vend varchar(10),deliDate smalldatetime) 采购表 －－－－－－－－－－－－－－－－－－－－ If not object_id('[V]') is null Drop table [V] Go create table V(Dt smalldatetime) insert into v select '2009-10-01' insert into v select '2009-10-02' insert into v select '2009-10-03' insert into v select '2009-10-04' insert into v select '2009-10-05' insert into v select '2009-10-06' insert into v select '2009-10-07' insert into v select '2010-2-05' insert into v select '2010-2-06' insert into v select '2010-2-07' If not object_id('[D]') is null Drop table [D] Go create table D(Vend varchar(10),deliDate smalldatetime) insert into d select 'aa','2009-10-4' insert into d select 'bb','2009-9-4' insert into d select 'bb','2009-9-6' go --创建函数，找到非节假日和周末： If not object_id('[fn_date]') is null Drop function [fn_date] Go create function fn_date(@dt smalldatetime) returns datetime as begin while exists(select 1 from v where dt=@dt) or datepart(w,@dt+@@datefirst-1) in(6,7) begin set @dt=dateadd(dd,-1,@dt) end return @dt end go select *,dbo.fn_date(deliDate) deliDate2 from D /* Vend deliDate deliDate2 ---------- ----------------------- ----------------------- aa 2009-10-04 00:00:00 2009-09-30 00:00:00.000 bb 2009-09-04 00:00:00 2009-09-04 00:00:00.000 bb 2009-09-06 00:00:00 2009-09-04 00:00:00.000 (3 行受影响) */ 本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/htl258/archive/2009/09/05/4522132.aspx