制作Calender--任意两个时间内的日历

最新推荐文章于 2023-01-16 15:44:26 发布

usher_gml

最新推荐文章于 2023-01-16 15:44:26 发布

阅读量607

点赞数

分类专栏： MSSQL 文章标签：日历 calendar null insert delete table

本文链接：https://blog.youkuaiyun.com/usher_gml/article/details/4664071

版权

MSSQL 专栏收录该内容

85 篇文章

订阅专栏

CREATE TABLE [dbo].[Calendar](
[Num] [int] IDENTITY(1,1) NOT NULL,
[Sunday] [varchar](20) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[Monday] [varchar](20) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[Tuesday] [varchar](20) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[Wednesday] [varchar](20) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[Thursday] [varchar](20) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[Friday] [varchar](20) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
[Saturday] [varchar](20) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL
) ON [PRIMARY]

Create Proc MYCalendar(@StartDate datetime,@EndDate datetime)
as
begin
declare @Weekday char(20)
DBCC CHECKIDENT(Calendar,RESEED,0)

if datediff(dd,getdate(),@StartDate)>=0
set @Weekday=case datediff(dd,getdate(),@StartDate)%7
when 5 then 'Sunday'
when 6 then 'Monday'
when 0 then 'Tuesday'
when 1 then 'Wednesday'
when 2 then 'Thursday'
when 3 then 'Friday'
when 4 then 'Saturday'
end
else
set @Weekday=case abs(datediff(dd,getdate(),@StartDate))%7
when 2 then 'Sunday'
when 1 then 'Monday'
when 0 then 'Tuesday'
when 6 then 'Wednesday'
when 5 then 'Thursday'
when 4 then 'Friday'
when 3 then 'Saturday'
end

while @StartDate<@EndDate+6
begin
if datediff(dd,getdate(),@StartDate)>=0
insert Calendar
select Sunday=case datediff(dd,getdate(),@StartDate)%7 when 5 then convert(varchar,@StartDate,101) else '' end,
       Monday=case datediff(dd,getdate(),@StartDate)%7 when 6 then convert(varchar,@StartDate,101) else '' end,
      Tuesday=case datediff(dd,getdate(),@StartDate)%7 when 0 then convert(varchar,@StartDate,101) else '' end,
    wednesday=case datediff(dd,getdate(),@StartDate)%7 when 1 then convert(varchar,@StartDate,101) else '' end,
     Thursday=case datediff(dd,getdate(),@StartDate)%7 when 2 then convert(varchar,@StartDate,101) else '' end,
       Friday=case datediff(dd,getdate(),@StartDate)%7 when 3 then convert(varchar,@StartDate,101) else '' end,
     Saturday=case datediff(dd,getdate(),@StartDate)%7 when 4 then convert(varchar,@StartDate,101) else '' end
else
insert Calendar
select Sunday=case abs(datediff(dd,getdate(),@StartDate))%7 when 2 then convert(varchar,@StartDate,101) else '' end,
       Monday=case abs(datediff(dd,getdate(),@StartDate))%7 when 1 then convert(varchar,@StartDate,101) else '' end,
      Tuesday=case abs(datediff(dd,getdate(),@StartDate))%7 when 0 then convert(varchar,@StartDate,101) else '' end,
    wednesday=case abs(datediff(dd,getdate(),@StartDate))%7 when 6 then convert(varchar,@StartDate,101) else '' end,
     Thursday=case abs(datediff(dd,getdate(),@StartDate))%7 when 5 then convert(varchar,@StartDate,101) else '' end,
       Friday=case abs(datediff(dd,getdate(),@StartDate))%7 when 4 then convert(varchar,@StartDate,101) else '' end,
     Saturday=case abs(datediff(dd,getdate(),@StartDate))%7 when 3 then convert(varchar,@StartDate,101) else '' end
set @StartDate=dateadd(day,7,@StartDate)
end

if @Weekday='Sunday'
begin
update Calendar set Monday=convert(varchar,dateadd(day,1,cast(Sunday as datetime)),101)
update Calendar set Tuesday=convert(varchar,dateadd(day,2,cast(Sunday as datetime)),101)
update Calendar set Wednesday=convert(varchar,dateadd(day,3,cast(Sunday as datetime)),101)
update Calendar set Thursday=convert(varchar,dateadd(day,4,cast(Sunday as datetime)),101)
update Calendar set Friday=convert(varchar,dateadd(day,5,cast(Sunday as datetime)),101)
update Calendar set Saturday=convert(varchar,dateadd(day,6,cast(Sunday as datetime)),101)
end
else if @Weekday='Monday'
begin
update Calendar set Sunday=convert(varchar,dateadd(day,-1,cast(Monday as datetime)),101)
update Calendar set Tuesday=convert(varchar,dateadd(day,1,cast(Monday as datetime)),101)
update Calendar set Wednesday=convert(varchar,dateadd(day,2,cast(Monday as datetime)),101)
update Calendar set Thursday=convert(varchar,dateadd(day,3,cast(Monday as datetime)),101)
update Calendar set Friday=convert(varchar,dateadd(day,4,cast(Monday as datetime)),101)
update Calendar set Saturday=convert(varchar,dateadd(day,5,cast(Monday as datetime)),101)
end
else if @Weekday='Tuesday'
begin
update Calendar set Sunday=convert(varchar,dateadd(day,-2,cast(Tuesday as datetime)),101)
update Calendar set Monday=convert(varchar,dateadd(day,-1,cast(Tuesday as datetime)),101)
update Calendar set Wednesday=convert(varchar,dateadd(day,1,cast(Tuesday as datetime)),101)
update Calendar set Thursday=convert(varchar,dateadd(day,2,cast(Tuesday as datetime)),101)
update Calendar set Friday=convert(varchar,dateadd(day,3,cast(Tuesday as datetime)),101)
update Calendar set Saturday=convert(varchar,dateadd(day,4,cast(Tuesday as datetime)),101)
end
else if @Weekday='Wednesday'
begin
update Calendar set Sunday=convert(varchar,dateadd(day,-3,cast(Wednesday as datetime)),101)
update Calendar set Monday=convert(varchar,dateadd(day,-2,cast(Wednesday as datetime)),101)
update Calendar set Tuesday=convert(varchar,dateadd(day,-1,cast(Wednesday as datetime)),101)
update Calendar set Thursday=convert(varchar,dateadd(day,1,cast(Wednesday as datetime)),101)
update Calendar set Friday=convert(varchar,dateadd(day,2,cast(Wednesday as datetime)),101)
update Calendar set Saturday=convert(varchar,dateadd(day,3,cast(Wednesday as datetime)),101)
end
else if @Weekday='Thursday'
begin
update Calendar set Sunday=convert(varchar,dateadd(day,-4,cast(Thursday as datetime)),101)
update Calendar set Monday=convert(varchar,dateadd(day,-3,cast(Thursday as datetime)),101)
update Calendar set Tuesday=convert(varchar,dateadd(day,-2,cast(Thursday as datetime)),101)
update Calendar set Wednesday=convert(varchar,dateadd(day,-1,cast(Thursday as datetime)),101)
update Calendar set Friday=convert(varchar,dateadd(day,-1,cast(Thursday as datetime)),101)
update Calendar set Saturday=convert(varchar,dateadd(day,-2,cast(Thursday as datetime)),101)
end
else if @Weekday='Friday'
begin
update Calendar set Sunday=convert(varchar,dateadd(day,-5,cast(Friday as datetime)),101)
update Calendar set Monday=convert(varchar,dateadd(day,-4,cast(Friday as datetime)),101)
update Calendar set Tuesday=convert(varchar,dateadd(day,-3,cast(Friday as datetime)),101)
update Calendar set Wednesday=convert(varchar,dateadd(day,-2,cast(Friday as datetime)),101)
update Calendar set Thursday=convert(varchar,dateadd(day,-1,cast(Friday as datetime)),101)
update Calendar set Saturday=convert(varchar,dateadd(day,1,cast(Friday as datetime)),101)
end
else
begin
update Calendar set Sunday=convert(varchar,dateadd(day,-6,cast(Saturday as datetime)),101)
update Calendar set Monday=convert(varchar,dateadd(day,-5,cast(Saturday as datetime)),101)
update Calendar set Tuesday=convert(varchar,dateadd(day,-4,cast(Saturday as datetime)),101)
update Calendar set Wednesday=convert(varchar,dateadd(day,-3,cast(Saturday as datetime)),101)
update Calendar set Thursday=convert(varchar,dateadd(day,-2,cast(Saturday as datetime)),101)
update Calendar set Friday=convert(varchar,dateadd(day,-1,cast(Saturday as datetime)),101)
end

end
go

create Proc DeleteDate(@StartDate datetime,@EndDate datetime)
as
begin

update Calendar set Sunday=''    where Num in (select Num from Calendar where cast(Sunday as datetime)<@StartDate or cast(Sunday as datetime)>@EndDate)
update Calendar set Monday=''    where Num in (select Num from Calendar where cast(Monday as datetime)<@StartDate or cast(Monday as datetime)>@EndDate)
update Calendar set Tuesday=''   where Num in (select Num from Calendar where cast(Tuesday as datetime)<@StartDate or cast(Tuesday as datetime)>@EndDate)
update Calendar set Wednesday='' where Num in (select Num from Calendar where cast(Wednesday as datetime)<@StartDate or cast(Wednesday as datetime)>@EndDate)
update Calendar set Thursday='' where Num in (select Num from Calendar where cast(Thursday as datetime)<@StartDate or cast(Thursday as datetime)>@EndDate)
update Calendar set Friday=''    where Num in (select Num from Calendar where cast(Friday as datetime)<@StartDate or cast(Friday as datetime)>@EndDate)
update Calendar set Saturday='' where Num in (select Num from Calendar where cast(Saturday as datetime)<@StartDate or cast(Saturday as datetime)>@EndDate)

delete Calendar where Sunday='' and Monday='' and Tuesday='' and Wednesday='' and Thursday='' and Friday='' and Saturday=''
end
go

exec MyCalendar '2009-6-1','2009-6-30'
exec DeleteDate '2009-6-1','2009-6-30'

select Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday from Calendar