近期有个功能需要判断某个给定日内（如 2012-01-06到2012-07-31）有多少个工作日，其中需要用到一个周末日期表，此表列出给定日期段内的所有属于周末的日期。
于是创建如下:
DROP TABLE #calendar CREATE TABLE #calendar (calendardate DATETIME) DECLARE @startdate DATETIME DECLARE @enddate DATETIME SET @startdate = ' 2012-01-01 ' SET @enddate = ' 2012-07-31 ' WHILE @startdate <= @enddate BEGIN INSERT INTO #calendar ( calendardate) SELECT @startdate SET @startdate = dateadd (DD, 1, @startdate) END SELECT * FROM #calendar SELECT CALENDARDATE, datename(dw,calendardate) AS DAYNAME , CASE WHEN datename(dw,calendardate) = ' Saturday ' THEN dateadd(mi, 390,calendardate) WHEN datename(dw,calendardate) = ' Monday ' THEN dateadd(ss, 23399,calendardate) ELSE calendardate END AS DELwithWEEKEND FROM #calendar WHERE datename(dw,calendardate) in ( ' Saturday ', ' Monday ') ORDER BY CALENDARDATE
还有另一个类似的解决方案，我们创建一个表，此表把给定日期段的每一天都归入某一类(eg:holiday, workday, EOMProcessdate....)如下图:
创建步骤如下:
1、创建一个临时表 create table #Corporate_Calendar ( Dateid int identity( 1, 1), Date datetime, Holiday bit default 0, Workday bit default 0, EOMProcessdate bit default 0, EOWProcessdate bit default 0, SpecialProcessdate bit default 0, Datestamp datetime default getdate()) go
2、在此临时表内填充所有日期。
你需要指定开始的日期以后此后跨度的天数，如此例，我们从2012年1月1号开始，往后创建365天，即2012年一年的数据。 declare @n int declare @maxn int declare @begindate datetime set @n = 1 set @maxn = 365 -- 日期表中需要创建的天数 set @begindate = convert( datetime, ' 01/01/2012 ') -- 日期表内的第一天 set @begindate = @begindate - 1 while @n <= @maxn begin insert into #Corporate_Calendar(date) select @begindate + @n set @n = @n + 1 end select * from #Corporate_Calendar
3、把日期表内的周末日期和工作日期分别标识出来
update #Corporate_Calendar set holiday = 1 where datename(dw,date) in ( ' Saturday ', ' Sunday ') go update #Corporate_Calendar set workday = 1 where holiday = 0 go
4、分别标识出每周最后一天，每月最后一天以及其它特殊处理日标识。如此例，我们标识2012-03-11是某周最后一天。
update #Corporate_Calendar set EOWProcessdate = 1 where date = ' 2012-03-11 '
5、标识出特殊的节假日，如此处我们标识出圣诞节和复活节
update #Corporate_Calendar set holiday = 1 where date = ' 12/25/2012 ' -- Xmas day go update #Corporate_Calendar set holiday = 1 where date = ' 3/25/2012 ' -- Easter go
6、如果我们把此临时表创建到数据库的正常数据表如内，我们可以通过创建一个Function来使用此表，判断某一天是否在我们需要特殊关照的某类日期内。
create function dbo.udf_isProcessDate ( @date datetime, @Type varchar( 10)) returns bit begin declare @x bit set @x = NULL If @type = ' Holiday ' select @x = holiday from dbo.Corporate_Calendar where convert( varchar( 10),date, 112) = convert( varchar( 10), @date, 112) If @type = ' Workday ' select @x = WorkDay from dbo.Corporate_Calendar where convert( varchar( 10),date, 112) = convert( varchar( 10), @date, 112) If @type = ' EOM ' select @x = EOMProcessdate from dbo.Corporate_Calendar where convert( varchar( 10),date, 112) = convert( varchar( 10), @date, 112) If @type = ' EOW ' select @x = EOWProcessdate from dbo.Corporate_Calendar where convert( varchar( 10),date, 112) = convert( varchar( 10), @date, 112) If @type = ' Special ' select @x = SpecialProcessdate from dbo.Corporate_Calendar where convert( varchar( 10),date, 112) = convert( varchar( 10), @date, 112) return @x end go
使用方法
-- select dbo.udf_isProcessDate(getdate(),'WorkDay')