SQL 时间查询的问题， ##################### 100% 结贴
根据两个时间点，然后查询出中间每个月的最后一天。。 怎么破？ 有什么好的办法？
例如 ： startime ：2012-11-05 ，endtime: 2013-2-15
需要的结果是
2012-11-30
2012-12-31
2013-01-31
2013-02-28（也不知道这是不是最后一天，不过大神们明白我的意思就好）
[解决办法]
上面的有点问题，修改一下：
declare @startime datetime
declare @endtime datetime
select @startime = '2012-11-05',
@endtime = '2013-2-15'
select convert(varchar(10),dateadd(day,-1,dateadd(month,1,convert(varchar(7),DATEADD(day,number,@startime),120)+'-01')),120) as date
from master..spt_values t
where type ='p' and t.number >=0 and
t.number <= DATEDIFF(DAY,@startime,@endtime)
group by convert(varchar(7),DATEADD(day,number,@startime),120)
/*
date
2012-11-30
2012-12-31
2013-01-31
2013-02-28
*/
/*
--创建函数
create function generateTime
(
@begin_date datetime,
@end_date datetime
)
returns @t table(date datetime)
as
begin
with maco as
(
select @begin_date AS date
union all
select date+1 from maco
where date+1 <=@end_date
)
insert into @t
select * from maco option(maxrecursion 0);
return
end
*/
DECLARE @startime DATE
SET @startime='2012-11-05'
DECLARE @endtime DATE
SET @endtime='2013-2-15'
SELECT DISTINCT CONVERT(DATE,DATEADD(DD,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,0,[date]),0)))[月末]
FROM dbo.generateTime(@startime,@endtime)
/*
月末
----------
2012-11-30
2012-12-31
2013-01-31
2013-02-28
*/
declare @startime DATETIME,@endtime DATETIME,@i int
select @startime = '2012-11-05',@endtime = '2013-2-15',@i=DATEDIFF(m,@startime,@endtime)+1
;WITH a1 AS
(
SELECT CAST(CONVERT(CHAR(7),DATEADD(m,1,@startime),120)+ '-01' AS DATETIME) startime,1 n
UNION ALL
SELECT DATEADD(m,1,startime),n+1 FROM a1
WHERE n+1<=@i
)
SELECT CONVERT(CHAR(10),DATEADD(d,-1,startime),120) month_end
FROM a1
