How to list all months and years (last three years inlcuding current year pus next three in this case) years?
--============================
--== View Month ==
--============================
alter view vw_Months
as
select top 100 percent * from udf_DBA_Months() order by Month_ID
--============================
--== Function Month ==
--============================
create function udf_DBA_Months ()
returns @Months TABLE (Month_ID int, Month_Name nvarchar(20))
as
--declare @Months TABLE (Month_ID int, Month_Name nvarchar(20))
BEGIN
declare @n int
declare @date smalldatetime
set @date = '20000101'
--select @date
set @n = 0
while @n <= 11
begin
insert into @Months
select @n+1,DATENAME(month, dateadd(month,@n,@date))
--select @date , @n, DATENAME(month, dateadd(month,@n,@date))
set @n = @n + 1
end
RETURN
END
select * from udf_DBA_Years ()
--============================
--== View Year ==
--============================
create view vw_Years
as
select top 100 percent * from udf_DBA_Years() order by Year_ID
--============================
--== Function Year ==
--============================
create function udf_DBA_Years ()
returns @Years TABLE (Year_ID int)
as
BEGIN
declare @n int
declare @year int
select @year = datepart(year,dateadd(year,-3,getdate()))
set @n = @year + 4
while @year <= @n
begin
insert into @Years
select @year
--select @date , @n, DATENAME(month, dateadd(month,@n,@date))
set @year = @year + 1
end
RETURN
END