exists(
select 1 from sysobjects
where type
=‘TF‘ and name
=‘ATPublic_TF_GetSummaryDate‘)
drop function ATPublic_TF_GetSummaryDate
go
create function ATPublic_TF_GetSummaryDate
(
@calltype varchar(
1)
=‘‘,
--0:年 1:月 1:日
@startdate DATE,
--开始日期
@enddate DATE
--结束日期
)
/***
Author:
Create date: 2018-01-18
Description: 循环返回日期
Example:
***/
RETURNS @retDateValue TABLE (datevalue
varchar(
32))
as
begin
if @calltype=‘0‘
begin
insert into @retDateValue(datevalue)
select substring(
convert(
varchar(
32),
DATEADD(
YEAR,
number,
@startdate),
21),
1,
4)
as yearvalue
from master..spt_values
WHERE TYPE
= ‘P‘ AND DATEADD(
YEAR,
number,
@startdate)
<= @enddate
end
else if @calltype=‘1‘
begin
insert into @retDateValue(datevalue)
select substring(
convert(
varchar(
32),
DATEADD(
MONTH,
number,
@startdate),
21),
1,
7)
as yearvalue
from master..spt_values
WHERE TYPE
= ‘P‘ AND DATEADD(
MONTH,
number,
@startdate)
< @enddate
end
else if @calltype=‘2‘
begin
insert into @retDateValue(datevalue)
select convert(
varchar(
32),
DATEADD(
DAY,
number,
@startdate),
21)
+‘ 00:00:00‘ as yearvalue
from master..spt_values
WHERE TYPE
= ‘P‘ AND DATEADD(
DAY,
number,
@startdate)
< @enddate
end
return
end
go
调用示例：
declare @beginDate varchar(32)=‘‘, @endDate varchar(32)=‘‘,@datebefore datetime
select @beginDate=‘2017-01-01‘,@endDate=‘2017-05-31‘
select @datebefore=CONVERT(datetime,@beginDate)
select datevalue from ATPublic_TF_GetSummaryDate(‘0‘,@beginDate,@endDate)
select datevalue from ATPublic_TF_GetSummaryDate(‘1‘,@beginDate,dateadd(YEAR,1,@datebefore))
select datevalue from ATPublic_TF_GetSummaryDate(‘1‘,@beginDate,dateadd(MONTH,1,@datebefore))
select datevalue from ATPublic_TF_GetSummaryDate(‘2‘,@beginDate,dateadd(MONTH,1,@datebefore))
sql server获取连续年份、月份、日
标签：art creat char ring type exists sys bst gets
