本文主要向大家介绍了SQLServer数据库之sql server 生成万年历，通过具体的内容向大家展现，希望对大家学习SQLServer数据库有所帮助。

--建了一张表
if exists (select * from dbo.sysobjects where id = object_id(N‘[dbo].[Bas_DateTime]‘) and OBJECTPROPERTY(id, N‘IsUserTable‘) = 1)drop table [dbo].[Bas_DateTime]GO
CREATE TABLE [dbo].[Bas_DateTime] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [Rq] [datetime] NULL , [YearOfRq] [varchar] (20) NULL , [HalfYear] [varchar] (20) NULL , [QuarterOfYear] [varchar] (20) NULL , [MonthOfYear] [varchar] (20) NULL , [WeekOfHalfYear] [varchar] (20) NULL , [WeekOfYear] [varchar] (20) NULL , [WeekOfQuarter] [varchar] (20) NULL , [WeekOfMonth] [varchar] (20) NULL , [WeekDay] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY]GO
--向表中插入日期declare @staDate datetime,@endDate datetimeSET @stadate=‘2000-01-01‘  --起始日期SET @endDate=‘2051-01-01‘  --结束日期while @staDate < @endDatebegin  insert into Bas_DateTime(Rq) values (@stadate)  SET @staDate=@StaDate+1endGo
--YearofRqupdate Bas_DateTimeset YearOfRq = ltrim(str(cast(Year(Rq)as int)))+‘年‘go--HalfYearupdate Bas_DateTimeset HalfYear = case when month(Rq)>6 then ‘下半年‘ else ‘上半年‘ endgo/*--QuarterOfHalfYearupdate Bas_DateTimeset QuarterOfHalfYear = case when (month(Rq) between  1 and 3 ) or (month(Rq) between  7 and  9 )then 1 else 2 endgo*/--QuarterOfYearupdate Bas_DateTimeset QuarterOfYear = case when (month(Rq) between  1 and 3 ) then ‘第一季度‘ when (month(Rq) between  4 and 6 ) then ‘第二季度‘when (month(Rq) between  7 and 9 ) then ‘第三季度‘ else ‘第四季度‘ endgo--WeekDayupdate Bas_DateTimeset [WeekDay]= DateName(weekday,Rq) go
--MonthOfHalfYear/*update Bas_DateTimeset MonthOfHalfYear = case when (Month(Rq) > 6 ) then (month(Rq)-6) else month(Rq) endgo*/--MonthOfYear
update Bas_DateTimeset MonthOfYear =  ltrim(str(cast(month(Rq)as int)))+‘月‘ go--weekofyearupdate Bas_DateTimeset WeekOfYear = ‘第‘+ltrim(str(cast(datepart(week,Rq)as int)))+‘周‘go
/*declare @ datetimeset @ = ‘2007-08-09‘select datename(weekday,dateadd(month,datediff(month,0,@),0)) as ‘周‘*/-- datediff(day,‘2007-08-01‘,‘2007-08-02‘)-- dateadd(month,datediff(month,0,@),0)  ‘        所在月的第一天--dateadd(quarter,datediff(quarter,0,@),0)   as   所在季的第一天-- dateadd(year,year(‘2007-02-9‘)-2000,‘2000-06-30‘)  以2000-06-30为基准，以后或得xxxx-06-30
--weekofmonthupdate Bas_DateTimeset weekofmonth = case when datename(weekday,dateadd(month,datediff(month,0,rq),0))=‘星期一‘ then (case   when datediff(day,dateadd(month,datediff(month,0,rq),0),rq)<6 then ‘第1周‘  when datediff(day,dateadd(month,datediff(month,0,rq),0),rq)<13 and="">=6 then ‘第2周‘  when datediff(day,dateadd(month,datediff(month,0,rq),0),rq)<20 and="">=13 then ‘第3周‘  when datediff(day,dateadd(month,datediff(month,0,rq),0),rq)<27 and="">=20 then ‘第4周‘  when datediff(day,dateadd(month,datediff(month,0,rq),0),rq)<32 and="">=27 then ‘第5周‘end )when datename(weekday,dateadd(month,datediff(month,0,rq),0))=‘星期二‘ then( case    when datediff(day,dateadd(month,datediff(month,0,rq),0),rq)<5 then ‘第1周‘  when datediff(day,dateadd(month,datediff(month,0,rq),0),rq)<12 and="">=5 then ‘第2周‘  when datediff(day,dateadd(month,datediff(month,0,rq),0),rq)<19 and="">=12 then ‘第3周‘  when datediff(day,dateadd(month,datediff(month,0,rq),0),rq)<26 and="">=19 then ‘第4周‘  when datediff(day,dateadd(month,datediff(month,0,rq),0),rq)<32 and="">=26 then ‘第5周‘end )when datename(weekday,dateadd(month,datediff(month,0,rq),0))=‘星期三‘ then( case   when datediff(day,dateadd(month,datediff(month,0,rq),0),rq)<4 then ‘第1周‘  when datediff(day,dateadd(month,datediff(month,0,rq),0),rq)<11 and="">=4 then ‘第2周‘  when datediff(day,dateadd(month,datediff(month,0,rq),0),rq)<18 and="">=11 then ‘第3周‘  when datediff(day,dateadd(month,datediff(month,0,rq),0),rq)<25 and="">=18 then ‘第4周‘  when datediff(day,dateadd(month,datediff(month,0,rq),0),rq)<32 and="">=25 then ‘第5周‘end )when datename(weekday,dateadd(month,datediff(month,0,rq),0))=‘星期四‘ then(case   when datediff(day,dateadd(month,datediff(month,0,rq),0),rq)<3 then ‘第1周‘  when datediff(day,dateadd(month,datediff(month,0,rq),0),rq)<10 and="">=3 then ‘第2周‘  when datediff(day,dateadd(month,datediff(month,0,rq),0),rq)<17 and="">=10 then ‘第3周‘  when datediff(day,dateadd(month,datediff(month,0,rq),0),rq)<24 and="">=17 then ‘第4周‘  when datediff(day,dateadd(month,datediff(month,0,rq),0),rq)<31 and="">=24 then ‘第5周‘  when datediff(day,dateadd(month,datediff(month,0,rq),0),rq) >=31 then ‘第6周‘end )when datename(weekday,dateadd(month,datediff(month,0,rq),0))=‘星期五‘ then(case   when datediff(day,dateadd(month,datediff(month,0,rq),0),rq)<2 then ‘第1周‘  when datediff(day,dateadd(month,datediff(month,0,rq),0),rq)<9 and="">=2 then ‘第2周‘  when datediff(day,dateadd(month,datediff(month,0,rq),0),rq)<16 and="">=9 then ‘第3周‘  when datediff(day,dateadd(month,datediff(month,0,rq),0),rq)<23 and="">=16 then ‘第4周‘  when datediff(day,dateadd(month,datediff(month,0,rq),0),rq)<30 and="">=23 then ‘第5周‘  when datediff(day,dateadd(month,datediff(month,0,rq),0),rq) >=30 then ‘第6周‘end )when datename(weekday,dateadd(month,datediff(month,0,rq),0))=‘星期六‘ then(case   when datediff(day,dateadd(month,datediff(month,0,rq),0),rq)<1 then ‘第1周‘  when datediff(day,dateadd(month,datediff(month,0,rq),0),rq)<8 and="">=1 then ‘第2周‘  when datediff(day,dateadd(month,datediff(month,0,rq),0),rq)<15 and="">=8 then ‘第3周‘  when datediff(day,dateadd(month,datediff(month,0,rq),0),rq)<22 and="">=15 then ‘第4周‘  when datediff(day,dateadd(month,datediff(month,0,rq),0),rq)<29 and="">=22 then ‘第5周‘  when datediff(day,dateadd(month,datediff(month,0,rq),0),rq) >=29 then ‘第6周‘end )when datename(weekday,dateadd(month,datediff(month,0,rq),0))=‘星期日‘ then(case   when datediff(day,dateadd(month,datediff(month,0,rq),0),rq)<7 then ‘第1周‘  when datediff(day,dateadd(month,datediff(month,0,rq),0),rq)<15 and="">=7 then ‘第2周‘  when datediff(day,dateadd(month,datediff(month,0,rq),0),rq)<22 and="">=15 then ‘第3周‘  when datediff(day,dateadd(month,datediff(month,0,rq),0),rq)<29 and="">=22 then ‘第4周‘  when datediff(day,dateadd(month,datediff(month,0,rq),0),rq)<32 and="">=29 then ‘第5周‘end)endgo
--weekofhalfyearupdate Bas_DateTimeset weekofhalfyear = case when month(rq) < 7 then weekofyear else casewhen datename(weekday,dateadd(year,year(rq)-2000,‘2000-06-30‘))=‘星期六‘ then ‘第‘+ltrim(str(cast(datepart(week,Rq)-datepart(week,dateadd(year,year(rq)-2000,‘2000-06-30‘))as int)))+‘周‘else ‘第‘+ltrim(str(cast(datepart(week,Rq)-datepart(week,dateadd(year,year(rq)-2000,‘2000-06-30‘)) + 1 as int)))+‘周‘ endendgo--weekofquarterupdate Bas_DateTimeset weekofquarter= case when month(Rq) between 1 and 3 then weekofyearwhen month(Rq) between 4 and 6 then case  when datename(weekday,dateadd(year,year(rq)-2000,‘2000-03-31‘))=‘星期六‘ then ‘第‘+ltrim(str(cast(datepart(week,Rq)-datepart(week,dateadd(year,year(rq)-2000,‘2000-03-31‘))as int)))+‘周‘  else ‘第‘+ltrim(str(cast(datepart(week,Rq)-datepart(week,dateadd(year,year(rq)-2000,‘2000-03-31‘))+1 as int)))+‘周‘ endwhen month(Rq) between 7 and 9 then weekofhalfyearwhen month(Rq) between 10 and 12 then case  when datename(weekday,dateadd(year,year(rq)-2000,‘2000-09-30‘))=‘星期六‘ then ‘第‘+ltrim(str(cast(datepart(week,Rq)-datepart(week,dateadd(year,year(rq)-2000,‘2000-09-30‘))as int)))+‘周‘   else ‘第‘+ltrim(str(cast(datepart(week,Rq)-datepart(week,dateadd(year,year(rq)-2000,‘2000-09-30‘))+1 as int)))+‘周‘ endendgoselect * from Bas_DateTime order by Rq

本文由职坐标整理并发布，希望对同学们学习SQL Server有所帮助，更多内容请关注职坐标数据库SQL Server数据库频道！