年 月 开始日期 结束日期
2015 1 2015-1-1 2015-1-31
2015 2 2015-2-1 2015-2-30
--------------------------
-----------------------
2045 12 2045-12-1 2045-12-30
------解决思路----------------------
with mt as (
select DATEADD(MONTH,number,'2015-01-01') startdate
from master..spt_values
where type = 'p'
and number < 12 * 30
)
select
YEAR(startdate) 年 ,
MONTH(startdate) 月,
startdate 开始日期,
DATEADD(day,-1,DATEADD(month,1,startdate)) 结束日期
from mt
年 月 开始日期 结束日期
----------- ----------- ----------------------- -----------------------
2015 1 2015-01-01 00:00:00.000 2015-01-31 00:00:00.000
2015 2 2015-02-01 00:00:00.000 2015-02-28 00:00:00.000
2015 3 2015-03-01 00:00:00.000 2015-03-31 00:00:00.000
2015 4 2015-04-01 00:00:00.000 2015-04-30 00:00:00.000
2015 5 2015-05-01 00:00:00.000 2015-05-31 00:00:00.000
2015 6 2015-06-01 00:00:00.000 2015-06-30 00:00:00.000
..................
2043 12 2043-12-01 00:00:00.000 2043-12-31 00:00:00.000
2044 1 2044-01-01 00:00:00.000 2044-01-31 00:00:00.000
2044 2 2044-02-01 00:00:00.000 2044-02-29 00:00:00.000
2044 3 2044-03-01 00:00:00.000 2044-03-31 00:00:00.000
2044 4 2044-04-01 00:00:00.000 2044-04-30 00:00:00.000
2044 5 2044-05-01 00:00:00.000 2044-05-31 00:00:00.000
2044 6 2044-06-01 00:00:00.000 2044-06-30 00:00:00.000
2044 7 2044-07-01 00:00:00.000 2044-07-31 00:00:00.000
2044 8 2044-08-01 00:00:00.000 2044-08-31 00:00:00.000
2044 9 2044-09-01 00:00:00.000 2044-09-30 00:00:00.000
2044 10 2044-10-01 00:00:00.000 2044-10-31 00:00:00.000
2044 11 2044-11-01 00:00:00.000 2044-11-30 00:00:00.000
2044 12 2044-12-01 00:00:00.000 2044-12-31 00:00:00.000
(360 行受影响)