日期:2014-05-17 浏览次数:20465 次
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 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)
*/
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('')