求一关于日期间隔的sql语句

[zywhao]

求一个标量值函数

参数：@dtmStart @dtmFinish
功能: 找出两个日期之间天的个位数



如@dtmStart="2013-05-25 10:0:01:035" @dtmStart="2013-06-07 10:0:01:035"
两个日期之间间隔日期为 5-25 5-26 5-27 5-28 5-29 5-30 5-31 6-1 6-2 6-3 6-4 6-5 6-6 6-7

只取每个日期天的个位数： 5，6，7，8，9，0，1，1，2，3，4，5，6，7

重复的合并一下，结果为 "5,6,7,8,9,0,1,2,3,4"

[哥眼神纯洁不]

declare @dtmStart  date ='2013-05-25 10:0:01:035'  
declare @dtmFinish date ='2013-06-07 10:0:01:035'
;
with tb as(
select a=@dtmStart 
union all
select DATEADD(day,1,a) from tb where a<@dtmFinish
)
select a=stuff((select ','+substring(convert(varchar,a),10,1) 
from tb for xml path('')),1,1,'')

[菜鸟之GrowUP]

CREATE FUNCTION sf_GetDateLastNum
(
  @BDate DATETIME,
  @EDate DATETIME
)
RETURNS VARCHAR(100)
AS
BEGIN
  DECLARE @RetVal VARCHAR(100)
  DECLARE @TBL TABLE(IID INT IDENTITY(1,1), LastDay VARCHAR(1))
  SET @RetVal = ''
 
  INSERT INTO @TBL(LastDay)
  SELECT RIGHT(DAY(DATEADD(DD, NUMBER, @BDate)), 1)
  FROM MASTER..SPT_VALUES 
    WHERE TYPE='p' AND DATEADD(DD, NUMBER, @BDate)<=@EDate
  
  DELETE @TBL WHERE IID NOT IN (SELECT MIN(IID) FROM @Tbl GROUP BY LastDay)
  
  SELECT @RetVal = @RetVal + LastDay + ',' FROM @TBL

  RETURN LEFT(@RetVal, LEN(@RetVal) - 1)
END
GO

SELECT dbo.sf_GetDateLastNum('2013-05-25 10:0:01:035', '2013-06-07 10:0:01:035')
/*
5,6,7,8,9,0,1,2,3,4
*/