SQL Server Generation Perpetual calendar

--a table was built

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] () NULL,
[Halfyear] [varchar] () NULL,
[Quarterofyear] [varchar] () NULL,
[Monthofyear] [varchar] () NULL,
[Weekofhalfyear] [varchar] () NULL,
[WeekOfYear] [varchar] () NULL,
[Weekofquarter] [varchar] () NULL,
[Weekofmonth] [varchar] () NULL,
[WeekDay] [varchar] (COLLATE) Chinese_prc_ci_as NULL
) on [PRIMARY]
GO

--Insert date into table
Declare @staDate datetime,
@endDate datetime
SET @stadate = ' 2000-01-01 '--start date
SET @endDate = ' 2051-01-01 '--end date
while @staDate < @endDate
Begin
INSERT INTO Bas_datetime (RQ) VALUES (@stadate)
SET @ [Email protected]+1
End
Go

--yearofrq
Update Bas_datetime
Set Yearofrq = LTrim (str ((RQ) as int)) + ' year '
Go
--halfyear
Update Bas_datetime
Set halfyear = case when month (Rq) >6 then ' second half ' of ' else ' first half ' end
Go
/*
--quarterofhalfyear
Update Bas_datetime
Set quarterofhalfyear = case when (month (RQ) between 1 and 3) or (for month (RQ) between 7 and 9) then 1 else 2 end
Go
*/
--quarterofyear
Update Bas_datetime
Set quarterofyear = case
When (Rq) between 1 and 3) Then ' first quarter '
When (RQ) between 4 and 6) Then ' second quarter '
When (RQ) between 7 and 9) Then ' third quarter '
Else ' Quarter ' end
Go
--weekday
Update Bas_datetime
Set [weekday]= datename (WEEKDAY,RQ)
Go

--monthofhalfyear
/*
Update Bas_datetime
Set monthofhalfyear = case when (month (RQ) > 6) Then (month (RQ)-6) Else month (RQ) end
go*/
--monthofyear

Update Bas_datetime
Set monthofyear = LTrim (str ((RQ) as int)) + ' month '
Go
--weekofyear
Update Bas_datetime
Set weekofyear = ' +ltrim ' (str (CAST (DATEPART (WEEK,RQ) as int)) + ' Week '
Go

/*declare @ datetime
SET @ = ' 2007-08-09 '
Select Datename (Weekday,dateadd (Month,datediff (month,0,@), 0) as ' Week '
*/
--DateDiff (day, ' 2007-08-01 ', ' 2007-08-02 ')
--DateAdd (Month,datediff (month,0,@), 0) ' The first day of the month
--dateadd (Quarter,datediff (quarter,0,@), 0) as the first day of the season
--DateAdd (Year,year (' 2007-02-9 ')-2000, ' 2000-06-30 ') is based on 2000-06-30, later or xxxx-06-30

--weekofmonth
Update Bas_datetime
Set weekofmonth = case when Datename (Weekday,dateadd (Month,datediff (MONTH,0,RQ), 0)) = ' Monday ' Then
When DateDiff (Day,dateadd (Month,datediff (MONTH,0,RQ), 0), RQ) <6 then ' 1th Week '
When DateDiff (Day,dateadd (Month,datediff (MONTH,0,RQ), 0), RQ) <13 and DateDiff (Day,dateadd (Month,datediff (month,0 , RQ), 0), RQ) >=6 then ' 2nd week '
When DateDiff (Day,dateadd (Month,datediff (MONTH,0,RQ), 0), RQ) <20 and DateDiff (Day,dateadd (Month,datediff (month,0 , RQ), 0), RQ) >=13 Then ' 3rd week '
When DateDiff (Day,dateadd (Month,datediff (MONTH,0,RQ), 0), RQ) <27 and DateDiff (Day,dateadd (Month,datediff (month,0 , RQ), 0), RQ) >=20 Then ' 4th Week '
When DateDiff (Day,dateadd (Month,datediff (MONTH,0,RQ), 0), RQ) <32 and DateDiff (Day,dateadd (Month,datediff (month,0 , RQ), 0), RQ) >=27 then ' 5th Week '
End)
When Datename (Weekday,dateadd (Month,datediff (MONTH,0,RQ), 0)) = ' Tuesday ' Then
When DateDiff (Day,dateadd (Month,datediff (MONTH,0,RQ), 0), RQ) <5 then ' 1th Week '
When DateDiff (Day,dateadd (Month,datediff (MONTH,0,RQ), 0), RQ) <12 and DateDiff (Day,dateadd (Month,datediff (month,0 , RQ), 0), RQ) >=5 then ' 2nd week '
When DateDiff (Day,dateadd (Month,datediff (MONTH,0,RQ), 0), RQ) <19 and DateDiff (Day,dateadd (Month,datediff (month,0 , RQ), 0), RQ) >=12 Then ' 3rd week '
When DateDiff (Day,dateadd (Month,datediff (MONTH,0,RQ), 0), RQ) <26 and DateDiff (Day,dateadd (Month,datediff (month,0 , RQ), 0), RQ) >=19 Then ' 4th Week '
When DateDiff (Day,dateadd (Month,datediff (MONTH,0,RQ), 0), RQ) <32 and DateDiff (Day,dateadd (Month,datediff (month,0 , RQ), 0), RQ) >=26 then ' 5th Week '
End)
When Datename (Weekday,dateadd (Month,datediff (MONTH,0,RQ), 0)) = ' Wednesday ' Then
When DateDiff (Day,dateadd (Month,datediff (MONTH,0,RQ), 0), RQ) <4 then ' 1th Week '
When DateDiff (Day,dateadd (Month,datediff (MONTH,0,RQ), 0), RQ) <11 and DateDiff (Day,dateadd (Month,datediff (month,0 , RQ), 0), RQ) >=4 then ' 2nd week '
When DateDiff (Day,dateadd (Month,datediff (MONTH,0,RQ), 0), RQ) <18 and DateDiff (Day,dateadd (Month,datediff (month,0 , RQ), 0), RQ) >=11 Then ' 3rd week '
When DateDiff (Day,dateadd (Month,datediff (MONTH,0,RQ), 0), RQ) <25 and DateDiff (Day,dateadd (Month,datediff (month,0 , RQ), 0), RQ) >=18 Then ' 4th Week '
When DateDiff (Day,dateadd (Month,datediff (MONTH,0,RQ), 0), RQ) <32 and DateDiff (Day,dateadd (Month,datediff (month,0 , RQ), 0), RQ) >=25 then ' 5th Week '
End)
When Datename (Weekday,dateadd (Month,datediff (MONTH,0,RQ), 0)) = ' Thursday ' Then
When DateDiff (Day,dateadd (Month,datediff (MONTH,0,RQ), 0), RQ) <3 then ' 1th Week '
When DateDiff (Day,dateadd (Month,datediff (MONTH,0,RQ), 0), RQ) <10 and DateDiff (Day,dateadd (Month,datediff (month,0 , RQ), 0), RQ) >=3 then ' 2nd week '
When DateDiff (Day,dateadd (Month,datediff (MONTH,0,RQ), 0), RQ) <17 and DateDiff (Day,dateadd (Month,datediff (month,0 , RQ), 0), RQ) >=10 Then ' 3rd week '
When DateDiff (Day,dateadd (Month,datediff (MONTH,0,RQ), 0), RQ) <24 and DateDiff (Day,dateadd (Month,datediff (month,0 , RQ), 0), RQ) >=17 Then ' 4th Week '
When DateDiff (Day,dateadd (Month,datediff (MONTH,0,RQ), 0), RQ) <31 and DateDiff (Day,dateadd (Month,datediff (month,0 , RQ), 0), RQ) >=24 then ' 5th Week '
When DateDiff (Day,dateadd (Month,datediff (MONTH,0,RQ), 0), RQ) >=31 then ' 6th Week '
End)
When Datename (Weekday,dateadd (Month,datediff (MONTH,0,RQ), 0)) = ' Friday ' Then
When DateDiff (Day,dateadd (Month,datediff (MONTH,0,RQ), 0), RQ) <2 then ' 1th Week '
When DateDiff (Day,dateadd (Month,datediff (MONTH,0,RQ), 0), RQ) <9 and DateDiff (Day,dateadd (Month,datediff (month,0), RQ), 0), RQ) >=2 then ' 2nd week '
When DateDiff (Day,dateadd (Month,datediff (MONTH,0,RQ), 0), RQ) <16 and DateDiff (Day,dateadd (Month,datediff (month,0 , RQ), 0), RQ) >=9 Then ' 3rd week '
When DateDiff (Day,dateadd (Month,datediff (MONTH,0,RQ), 0), RQ) <23 and DateDiff (Day,dateadd (Month,datediff (month,0 , RQ), 0), RQ) >=16 Then ' 4th Week '
When DateDiff (Day,dateadd (Month,datediff (MONTH,0,RQ), 0), RQ) <30 and DateDiff (Day,dateadd (Month,datediff (month,0 , RQ), 0), RQ) >=23 then ' 5th Week '
When DateDiff (Day,dateadd (Month,datediff (MONTH,0,RQ), 0), RQ) >=30 then ' 6th Week '
End)
When Datename (Weekday,dateadd (Month,datediff (MONTH,0,RQ), 0)) = ' Saturday ' Then
When DateDiff (Day,dateadd (Month,datediff (MONTH,0,RQ), 0), RQ) <1 then ' 1th Week '
When DateDiff (Day,dateadd (Month,datediff (MONTH,0,RQ), 0), RQ) <8 and DateDiff (Day,dateadd (Month,datediff (month,0), RQ), 0), RQ) >=1 then ' 2nd week '
When DateDiff (Day,dateadd (Month,datediff (MONTH,0,RQ), 0), RQ) <15 and DateDiff (Day,dateadd (Month,datediff (month,0 , RQ), 0), RQ) >=8 Then ' 3rd week '
When DateDiff (Day,dateadd (Month,datediff (MONTH,0,RQ), 0), RQ) <22 and DateDiff (Day,dateadd (Month,datediff (month,0 , RQ), 0), RQ) >=15 Then ' 4th Week '
When DateDiff (Day,dateadd (Month,datediff (MONTH,0,RQ), 0), RQ) <29 and DateDiff (Day,dateadd (Month,datediff (month,0 , RQ), 0), RQ) >=22 then ' 5th Week '
When DateDiff (Day,dateadd (Month,datediff (MONTH,0,RQ), 0), RQ) >=29 then ' 6th Week '
End)
When Datename (Weekday,dateadd (Month,datediff (MONTH,0,RQ), 0)) = ' Sunday ' Then
When DateDiff (Day,dateadd (Month,datediff (MONTH,0,RQ), 0), RQ) <7 then ' 1th Week '
When DateDiff (Day,dateadd (Month,datediff (MONTH,0,RQ), 0), RQ) <15 and DateDiff (Day,dateadd (Month,datediff (month,0 , RQ), 0), RQ) >=7 then ' 2nd week '
When DateDiff (Day,dateadd (Month,datediff (MONTH,0,RQ), 0), RQ) <22 and DateDiff (Day,dateadd (Month,datediff (month,0 , RQ), 0), RQ) >=15 Then ' 3rd week '
When DateDiff (Day,dateadd (Month,datediff (MONTH,0,RQ), 0), RQ) <29 and DateDiff (Day,dateadd (Month,datediff (month,0 , RQ), 0), RQ) >=22 Then ' 4th Week '
When DateDiff (Day,dateadd (Month,datediff (MONTH,0,RQ), 0), RQ) <32 and DateDiff (Day,dateadd (Month,datediff (month,0 , RQ), 0), RQ) >=29 then ' 5th Week '
End
End
Go

--weekofhalfyear
Update Bas_datetime
Set weekofhalfyear = case when month (RQ) < 7 then WeekOfYear
else case
When Datename (Weekday,dateadd (Year,year (RQ)-2000, ' 2000-06-30 ')) = ' Saturday ' Then ' +ltrim (str (DATEPART (WEEK,RQ)- DatePart (Week,dateadd (year,year (RQ) -2000, ' 2000-06-30 ')))) + ' Week '
Else ' +ltrim ' (str (DATEPART (WEEK,RQ)-datepart (Week,dateadd (Rq) -2000, ' year,year ') + 1 as int)) + ' Week ' End
End
Go
--weekofquarter
Update Bas_datetime
Set weekofquarter= case
When month (RQ) between 1 and 3 then WeekOfYear
When month (RQ) between 4 and 6 then case
When Datename (Weekday,dateadd (Year,year (RQ)-2000, ' 2000-03-31 ')) = ' Saturday ' Then ' +ltrim (str (DATEPART (WEEK,RQ)- DatePart (Week,dateadd (year,year (RQ) -2000, ' 2000-03-31 ')))) + ' Week '
Else ' +ltrim (str (DATEPART (WEEK,RQ)-datepart (Week,dateadd (Rq) -2000, ' year,year ') +1 as int)) + ' Week ' End
When month (RQ) between 7 and 9 then Weekofhalfyear
When month (RQ) between and ten then case
When Datename (Weekday,dateadd (Year,year (RQ)-2000, ' 2000-09-30 ')) = ' Saturday ' Then ' +ltrim (str (DATEPART (WEEK,RQ)- DatePart (Week,dateadd (year,year (RQ) -2000, ' 2000-09-30 ')))) + ' Week '
Else ' +ltrim (str (DATEPART (WEEK,RQ)-datepart (Week,dateadd (Rq) -2000, ' year,year ') +1 as int)) + ' Week ' End
End
Go
SELECT * from Bas_datetime ORDER by Rq

SQL Server Generation Perpetual calendar