SQL日历表数据的简单生成

为了公司考勤系统的需要

编写的几个简单存储过程(可以手动运行,也可以设置事务自动运行!感觉还行比较通用,写出来共享下)

 

 

Calendar表结构很简单,2个字段:

fdDate 日期
fdType 考勤类型(工作日N,周末W,节假日H[需要根据需要自己修改])

 

--判断一段时间范围内的工作日（N）和周末（W）

Create     PROCEDURE [dbo].[NewMonthWeekDay_Calendar]
@sdate smalldatetime,
@edate smalldatetime
AS
declare @fdDate smalldatetime
declare @WeekDay varchar(20)
declare cr0 cursor for
 select fdDate from calendar where fddate>=@sdate and fddate<=@edate
open cr0
fetch next from cr0 into @fdDate
 while @@fetch_status=0
 begin
  if (datename(weekday,@fdDate)='星期一')
   update calendar set fdType='N' where fdDate=@fdDate
  if (datename(weekday,@fdDate)='星期二&