SQL日历表数据的简单生成

最新推荐文章于 2025-01-17 17:41:36 发布

原创最新推荐文章于 2025-01-17 17:41:36 发布 · 2.2k 阅读

2 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#日历 #calendar #sql #工作 #table

这篇博客介绍了如何使用SQL存储过程生成日历表，包括插入特定年份月份的日期，以及更新工作日和周末的标识。通过NewMonth_Calendar和NewMonthWeekDay_Calendar两个存储过程，可以实现自动填充和更新日历表的功能。

为了公司考勤系统的需要

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

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)='星期二')
   update calendar set fdType='N' where fdDate=@fdDate
  if (datename(weekday,@fdDate)='星期三')
   update calendar set fdType='N' where fdDate=@fdDate
  if (datename(weekday,@fdDate)='星期四')
   update calendar set fdType='N' where fdDate=@fdDate
  if (datename(weekday,@fdDate)='星期五')
   update calendar set fdType='N' where fdDate=@fdDate
  if (datename(weekday,@fdDate)='星期六')
   update calendar set fdType='W' where fdDate=@fdDate
  if (datename(weekday,@fdDate)='星期日')
   update calendar set fdType='W' where fdDate=@fdDate

  fetch next from cr0 into @fdDate

end
close cr0
deallocate cr0

--根据年和月自动插入Calendar表新日期数据

Create PROCEDURE [dbo].[NewMonth_Calendar]
@Year int,
@Month int
AS

insert into cas..calendar(fdDate)
Select Convert(Varchar(10), DateAdd(dd, ID, Cast(Rtrim(@Year) + '-' + Rtrim(@Month) + '-' + '01' As DateTime)), 120) as fdDate
From (SELECT TOP 50 ID=number FROM MASTER..SPT_VALUES WHERE TYPE='P' )T
Where T.ID < DateDiff(dd, Cast(Rtrim(@Year) + '-' + Rtrim(@Month) + '-' + '01' As DateTime),
DateAdd(mm, 1, Cast(Rtrim(@Year) + '-' + Rtrim(@Month) + '-' + '01' As DateTime)))

--sql2000可以使用这个
Select TOP 50 ID = Identity(Int, 0, 1) Into #T From SysColumns
insert into cas..calendar(fdDate)
Select Convert(Varchar(10), DateAdd(dd, ID, Cast(Rtrim(@Year) + '-' + Rtrim(@Month) + '-' + '01' As DateTime)), 120) From #T
Where ID < DateDiff(dd, Cast(Rtrim(@Year) + '-' + Rtrim(@Month) + '-' + '01' As DateTime), DateAdd(mm, 1, Cast(Rtrim(@Year) + '-' + Rtrim(@Month) + '-' + '01' As DateTime)))
Drop Table #T

--插入日历表全年的数据，并自动判断工作日（N）周末（W）

Create PROCEDURE [dbo].[NewYearInsrt]
@Year int
AS
if(@Year>2008)
begin
exec NewMonth_Calendar @Year,1
exec NewMonth_Calendar @Year,2
exec NewMonth_Calendar @Year,3
exec NewMonth_Calendar @Year,4
exec NewMonth_Calendar @Year,5
exec NewMonth_Calendar @Year,6
exec NewMonth_Calendar @Year,7
exec NewMonth_Calendar @Year,8
exec NewMonth_Calendar @Year,9
exec NewMonth_Calendar @Year,10
exec NewMonth_Calendar @Year,11
exec NewMonth_Calendar @Year,12
declare @sdate smalldatetime
declare @edate smalldatetime
set @sdate=cast(cast(@Year as varchar(4))+'-1-1' as smalldatetime)
set @edate=cast(cast(@Year as varchar(4))+'-12-31' as smalldatetime)
exec NewMonthWeekDay_Calendar @sdate,@edate
end

--直接运行 EXEC [dbo].[NewYearInsrt] 2010 即可