SQL2008 存储过程 传到起始日期,终止日期.返回一个由日期天的个位数组成的数组
SQL2008 存储过程 传入起始日期,终止日期.返回一个由日期天的个位数组成的数组
我现在需要写一个存储过程,实现以下功能:
传入起始日期,终止日期.返回一个由日期天的个位数组成的数组.
例如传入2012-11-28,2012-12-7
返回 8,9,0,1,2,3,4,5,6,7
请大神指教该怎么写.
------最佳解决方案--------------------
------其他解决方案--------------------
create proc pr_name(@Btime datetime,@Etime datetime)
as
begin
select
stuff((select ','+right(day(dateadd(day,number,@Btime)),1)
from master..spt_values where type='p'
and number<=datediff(day,@Btime,@Etime) for xml path('')),1,1,'')
end
exec pr_name '2012-11-28','2012-12-7'
/*
-------------------
8,9,0,1,2,3,4,5,6,7
(1 row(s) affected)
------其他解决方案--------------------
------其他解决方案--------------------
create proc P_date
@begindate date,@enddate date
as
begin
declare @T varchar(max)
set @T=''
while datediff(day,@begindate,@enddate)>0
begin
set @T=@T+','+right(cast(day(@begindate) as varchar(2)),1)
set @begindate=dateadd(day,1,@begindate)
end
select stuff(@T,1,1,'')
end
------其他解决方案--------------------
我现在需要写一个存储过程,实现以下功能:
传入起始日期,终止日期.返回一个由日期天的个位数组成的数组.
例如传入2012-11-28,2012-12-7
返回 8,9,0,1,2,3,4,5,6,7
请大神指教该怎么写.
------最佳解决方案--------------------
if OBJECT_ID('pro_test') is not null
drop proc pro_test
go
create proc pro_test
(
@Sdt smalldatetime,
@Edt smalldatetime
)
as
declare @str varchar(max)
set @str=''
;with t
as(
select
dateadd(dd,number,@Sdt) as dt
from
master..spt_values
where
number between 0 and datediff(dd,@Sdt,@Edt)
and type='p'
)
select
@str=@str+','+right(ltrim(day(dt)),1) from t
print right(@str,len(@str)-1)
go
exec pro_test '2012-11-28','2012-12-7'
/*
8,9,0,1,2,3,4,5,6,7
*/
------其他解决方案--------------------
create proc pr_name(@Btime datetime,@Etime datetime)
as
begin
select
stuff((select ','+right(day(dateadd(day,number,@Btime)),1)
from master..spt_values where type='p'
and number<=datediff(day,@Btime,@Etime) for xml path('')),1,1,'')
end
exec pr_name '2012-11-28','2012-12-7'
/*
-------------------
8,9,0,1,2,3,4,5,6,7
(1 row(s) affected)
------其他解决方案--------------------
create proc promep
(@b date,
@e date)
as
begin
declare @r varchar(max),@i int,@j int
select @r='',@i=0,@j=datediff(d,@b,@e)
while(@i<=@j)
begin
select @r=@r+right(cast(dateadd(d,@i,@b) as varchar(20)),1)+','
select @i=@i+1
end
select left(@r,len(@r)-1) '返回'
end
exec promep '2012-11-28','2012-12-7'
/*
返回
--------------------------
8,9,0,1,2,3,4,5,6,7
(1 row(s) affected)
*/
------其他解决方案--------------------
create proc P_date
@begindate date,@enddate date
as
begin
declare @T varchar(max)
set @T=''
while datediff(day,@begindate,@enddate)>0
begin
set @T=@T+','+right(cast(day(@begindate) as varchar(2)),1)
set @begindate=dateadd(day,1,@begindate)
end
select stuff(@T,1,1,'')
end
------其他解决方案--------------------
USE test
GO
DECLARE @startDate DATETIME,@endDate DATETIME
SELECT @startDate='2012-11-28'
,@endDate='2013-1-7'
-- SQL SERVER 2005
SELECT STUFF((
SELECT
','+RIGHT(Day(Day),1)
FROM (
SELECT
DATEADD(dd,number,@startDate) AS [Day]
FROM (
SELECT
number
FROM master..spt_values
WHERE type='p'
AND number<=DATEDIFF(dd,@startDate,@endDate)
) AS t
) AS o FOR XML PATH('')