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) goexec 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,'') endexec 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
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 DATETIMESELECT @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('') ),1,1,'')
-- SQL SERVER 2000 DECLARE @sql NVARCHAR(MAX)SELECT @sql=ISNULL(@sql+',','')+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
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)
goexec pro_test '2012-11-28','2012-12-7'
/*
8,9,0,1,2,3,4,5,6,7*/
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,'')
endexec 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)
*/
@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
GO
DECLARE @startDate DATETIME,@endDate DATETIMESELECT @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('')
),1,1,'')
-- SQL SERVER 2000
DECLARE @sql NVARCHAR(MAX)SELECT
@sql=ISNULL(@sql+',','')+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
SELECT @sql