求一关于日期间隔的sql语句
求一个标量值函数
参数：@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,'')
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
*/
