此随笔主在分享日常可能用到的sql函数，用于生成连续日期（年份、月份、日期）
具体的看代码及效果吧！
-- =============================================
-- Author: <Author,Jearay>
-- Create date: <Create Date,2018/7/12>
-- Description: <Description,返回连续日期（年份或月份或日期）>
-- =============================================
CREATE FUNCTION [dbo].[fn_GetContinuousDate]
(
@date datetime, --基准日期
@type nvarchar(10),--'year、y','month、mon、m','day、d','yearmonth、ym','monthday、md'
@prev int, --往前数量
@next int --后续数量
)
RETURNS
@return TABLE
(
DataDate date,DateAlis nvarchar(20),DateCommon nvarchar(20)
)
AS
BEGIN
declare @tempDate date,@tempDateAlis nvarchar(20),@tempDateCommon nvarchar(20),@index int=1
--年份
if LOWER(@type)=N'year' or LOWER(@type)=N'y'
begin
set @date=dateadd(year,DATEDIFF(year,0,@date),0)
--写入往前数量的年份
while @prev>0
begin
set @tempDate=dateadd(year,-@prev,@date)
insert @return
select @tempDate,cast(year(@tempDate) as nvarchar(4))+N'年',cast(year(@tempDate) as nvarchar(4))
set @prev=@prev-1
end
--写入当年
insert @return
select @date,cast(year(@date) as nvarchar(4))+N'年',cast(year(@date) as nvarchar(4))
--写入后续数量的年份
while @next-@index>=0
begin
set @tempDate=dateadd(year,@index,@date)
insert @return
select @tempDate,cast(year(@tempDate) as nvarchar(4))+N'年',cast(year(@tempDate) as nvarchar(4))
set @index=@index+1
end
end
--月份
else if LOWER(@type)=N'month' or LOWER(@type)=N'm' or LOWER(@type)=N'mon'
begin
set @date=dateadd(month,DATEDIFF(month,0,@date),0)
--写入往前数量的月份
while @prev>0
begin
set @tempDate=dateadd(month,-@prev,@date)
insert @return
select @tempDate,cast(year(@tempDate) as nvarchar(4))+N'年'+cast(month(@tempDate) as nvarchar(2))+N'月',cast(year(@tempDate) as nvarchar(4))+N'/'+cast(month(@tempDate) as nvarchar(2))
set @prev=@prev-1
end
--写入当月
insert @return
select @date,cast(year(@date) as nvarchar(4))+N'年'+cast(month(@date) as nvarchar(2))+N'月',cast(year(@date) as nvarchar(4))+N'/'+cast(month(@date) as nvarchar(2))
--写入后续数量的月份
while @next-@index>=0
begin
set @tempDate=dateadd(month,@index,@date)
insert @return
select @tempDate,cast(year(@tempDate) as nvarchar(4))+N'年'+cast(month(@tempDate) as nvarchar(2))+N'月',cast(year(@tempDate) as nvarchar(4))+N'/'+cast(month(@tempDate) as nvarchar(2))
set @index=@index+1
end
end
--日期
else if LOWER(@type)=N'day' or LOWER(@type)=N'd'
begin
set @date=dateadd(day,DATEDIFF(day,0,@date),0)
--写入往前数量的日期
while @prev>0
begin
set @tempDate=dateadd(day,-@prev,@date)
insert @return
select @tempDate,cast(year(@tempDate) as nvarchar(4))+N'年'+cast(month(@tempDate) as nvarchar(2))+N'月'+cast(day(@tempDate) as nvarchar(2))+N'日'
,cast(year(@tempDate) as nvarchar(4))+N'/'+cast(month(@tempDate) as nvarchar(2))+N'/'+cast(day(@tempDate) as nvarchar(2))
set @prev=@prev-1
end
--写入当日
insert @return
select @date,cast(year(@date) as nvarchar(4))+N'年'+cast(month(@date) as nvarchar(2))+N'月'+cast(day(@date) as nvarchar(2))+N'日'
,cast(year(@date) as nvarchar(4))+N'/'+cast(month(@date) as nvarchar(2))+N'/'+cast(day(@date) as nvarchar(2))
--写入后续数量的日期
while @next-@index>=0
begin
set @tempDate=dateadd(day,@index,@date)
insert @return
select @tempDate,cast(year(@tempDate) as nvarchar(4))+N'年'+cast(month(@tempDate) as nvarchar(2))+N'月'+cast(day(@tempDate) as nvarchar(2))+N'日'
,cast(year(@tempDate) as nvarchar(4))+N'/'+cast(month(@tempDate) as nvarchar(2))+N'/'+cast(day(@tempDate) as nvarchar(2))
set @index=@index+1
end
end
--年中月
else if LOWER(@type)=N'yearmonth' or LOWER(@type)=N'ym'
begin
set @date=dateadd(year,DATEDIFF(year,0,@date),0)
set @index=0
--写入年对应月份
while 12-@index>0
begin
set @tempDate=dateadd(month,@index,@date)
insert @return
select @tempDate,cast(month(@tempDate) as nvarchar(2))+N'月'
,cast(year(@tempDate) as nvarchar(4))+N'/'+cast(month(@tempDate) as nvarchar(2))
set @index=@index+1
end
end
--月中日, 分自然月和指定月
else if LOWER(@type)=N'monthday' or LOWER(@type)=N'md'
begin
--指定月
--指定月开始日期、结束日期
if @prev>0 and @next>0
begin
declare @endDate date
set @date=dateadd(month,DATEDIFF(month,0,@date),0) --获取月份
set @endDate=dateadd(day,@next,@date)
set @index=datediff(day,@endDate,dateadd(day,@prev-1,dateadd(month,-1,@date)))
--写入月对应日期
while @index<0
begin
set @tempDate=dateadd(day,@index,@endDate)
insert @return
select @tempDate,cast(year(@tempDate) as nvarchar(4))+N'年'+cast(month(@tempDate) as nvarchar(2))+N'月'+cast(day(@tempDate) as nvarchar(2))+N'日'
,@tempDate
set @index=@index+1
end
end
--自然月
else
begin
set @date=dateadd(month,DATEDIFF(month,0,@date),0)
set @index=datediff(day,dateadd(month,1,@date),@date)
set @date=dateadd(month,1,@date)
--写入月对应日期
while @index<0
begin
set @tempDate=dateadd(day,@index,@date)
insert @return
select @tempDate,cast(year(@tempDate) as nvarchar(4))+N'年'+cast(month(@tempDate) as nvarchar(2))+N'月'+cast(day(@tempDate) as nvarchar(2))+N'日'
,@tempDate
set @index=@index+1
end
end
end
RETURN
END
View Code
函数调用示例：
--返回今天往前3天至今天往后2天的连续日期
select * from dbo.fn_GetContinuousDate(getdate(),'d',3,2)
结果如下：
