SQL Server：生成指定年份的所有日期

测试所用的表：table24

 

结构及意义：

field1：主键序号

field2：日期

field3：当前日期是本年度的第几周（用来查询）

field4：星期几（如星期一）

field5：年份

 

建表SQL：

CREATE TABLE [dbo].[table24](
	[field1] [int] IDENTITY(1,1) NOT NULL,
	[field2] [datetime] NULL,
	[field3] [int] NULL,
	[field4] [varchar](10) COLLATE Chinese_PRC_CI_AS NULL,
	[field5] [int] NULL,
 CONSTRAINT [PK_table24] PRIMARY KEY CLUSTERED 
(
	[field1] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'主键-序号' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'table24', @level2type=N'COLUMN', @level2name=N'field1'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'日期' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'table24', @level2type=N'COLUMN', @level2name=N'field2'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'当前是本年的第几周' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'table24', @level2type=N'COLUMN', @level2name=N'field3'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'星期几' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'table24', @level2type=N'COLUMN', @level2name=N'field4'

GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'年份' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'table24', @level2type=N'COLUMN', @level2name=N'field5'

 

生成指定年份的所有日期：

--测试： 
--测试默认值：exec getDateOfThisYear 0,0 ;
--测试指定年份(补充以前的数据，不生成周末的数据)：exec getDateOfThisYear 2017,1,0;
--测试指定年份(不补充以前的数据，不生成周末的数据)：exec getDateOfThisYear 2017,0,0;
--测试指定年份(不补充以前的数据，生成周末的数据)：exec getDateOfThisYear 2017,0,1;
--测试指定年份(不补充以前的数据，不生成周末的数据)：exec getDateOfThisYear 2017,0,0;
--查看数据：select * from table24;
ALTER PROCEDURE [dbo].[getDateOfThisYear]	
	@cur_year int, --当前年份
	@flag int, --是否补充以前的数据，1-补充，将当前系统中最晚时间以前的数据补充上去 0-不补充，直接从最晚时间开始，默认为不补充直接从系统最晚时间开始
	@flag2 int --是否生成周六周日的数据，1-生成周六日的数据 0-不生成，默认不生成
AS
BEGIN	
	SET NOCOUNT ON;
	if(@cur_year is null or @cur_year<=0)
	begin
		set @cur_year = year(getDate());--赋值为当前年份
	end
	
	if(@flag is null or @flag<0)
	begin
		set @flag = 0;--不补充，从系统最晚时间开始
	end
	
	if(@flag2 is null or @flag2<0)
	begin
		set @flag2 = 0;--不生成周末时间
	end

	--开始时间	
	declare @startTime varchar(10);
	if(@flag=0)
	begin
		--获取系统中今年最大的时间
		select top 1 @startTime = dateadd(day,1,field2) from table24 where year(field2)=@cur_year order by field2 desc;--降序取第一条，并且计算出开始的时间		
		if(@startTime is null)--没有数据，取本周第一天,即周一
		begin
			set @startTime = dateadd(wk, datediff(wk,0,getdate()), 0);			
		end
	end
	else
	begin--补充以前的，那么从1月1号开始
		set @startTime = cast(@cur_year as varchar(20))+'0101';--形如20170101
	end
	
	--结束时间
	declare @endTime varchar(10);
	set @endTime = cast((@cur_year+1) as varchar(20))+'0101';--形如20180101

	--生成当前年份的数据
	if(@flag2=1)--生成周六日的时间
	begin
		insert into table24(field2,field5)
		select distinct dateadd(day,number,@startTime),@cur_year 
		from master..spt_values 
		where number>=0 and number<=365
		and dateadd(day,number,@startTime) <@endTime
		and dateadd(day,number,@startTime)  not in (select field2 from table24)--不允许重复
	end
	else--不生成
	begin
		insert into table24(field2,field5)
		select distinct dateadd(day,number,@startTime),@cur_year 
		from master..spt_values 
		where number>=0 and number<=365
		and dateadd(day,number,@startTime) <@endTime
		and datepart(dw,dateadd(day,number,@startTime)) not in (1,7)--not in (1,7)：不生成周六日的数据
		and dateadd(day,number,@startTime)  not in (select field2 from table24)--不允许重复
	end
	--更新今年每个日期是是第几周，是星期几
	update table24 set field3=datepart(week,field2),field4=datename(dw,field2) where field5=@cur_year;

	--更新以前的数据
	--update table24 set field3=datepart(week,field2),field4=datename(dw,field2),field5=year(field2) where field4 is null or field5 is null;
END
--select datepart(weekday,getdate()) as '周内的第几日'
--select datepart(week,getdate()) as '年内的第几周'
--select datepart(quarter,getdate()) as 年内的第几季'
--select datename(dw,getdate()) as '当前是星期几' --可返回汉字形式的星期
--select datepart(dw,'20170909') as '当前是星期几' --可返回数字形式的星期(1-星期日 2-星期一 3-星期二 以此类推)
/*--生成一年的数据
insert tb
 select distinct dateadd(day,number,'20130101') 
 from master..spt_values 
 where number>=0 and number<=365
 and dateadd(day,number,'20130101') <'20140101'
 and dateadd(day,number,'20130101')  not in (select date from tb)
*/

 

生成本周的所有日期：

--测试： exec getDateOfWeek;
CREATE PROCEDURE [dbo].[getDateOfWeek]	
AS
BEGIN	
	SET NOCOUNT ON;
	/*	
	--获取当前周的第一天，也就是这周的周一是哪天
	declare @mon datetime;
	select @mon = dateadd(wk, datediff(wk,0,getdate()), 0);
	*/
	--获取当前周是第几周
	declare @weekes int;
	select @weekes = dbo.WeekOfMonth(getDate());
	
	--循环添加数据
	declare @count int;
	declare @c int;
	set @count = 6;
	set @c = 0;
	while(@c<=@count)
	begin
		insert into table24(field2,field5)
		select dateadd(wk, datediff(wk,0,getdate()), @c),year(getDate());
		set @c = @c +1;
	end

	update table24 set field3=datepart(week,field2),field4=datename(dw,field2) where field5=year(getDate());
END