27,579
社区成员
发帖
与我相关
我的任务
分享
我现在想取一个时间段内所有的月份:如想取2012-08-15 到2013-01-10这个时间段之内的所有月份
要求返回值1:
查询月份
2012-08-01
2012-09-01
2012-10-01
2012-11-01
2012-12-01
2013-01-01
返回值2:
查询年份 查询月份
2012 8
2012 9
2012 10
2012 11
2012 12
2013 1
DECLARE @begin DATETIME, @end DATETIME
SELECT @begin = '2012-03-31', @end = '2013-03-31'
select year(dateadd(m, number, @begin)), month(dateadd(m, number, @begin))
from master..spt_values
where type = 'p'
and number <= DATEDIFF(month, @begin, @end)
DECLARE @begin DATETIME, @end DATETIME
SELECT @begin = '2012-03-31', @end = '2013-03-31'
SELECT TOP(DATEDIFF(MONTH, @begin, @end)+1)
themonth = CONVERT(CHAR(8),DATEADD(MONTH, number, @begin), 120)+'01'
FROM master..spt_values
WHERE type = 'p'
ORDER BY number
SELECT TOP(DATEDIFF(MONTH, @begin, @end)+1)
theyear = YEAR(DATEADD(MONTH, number, @begin)),
themonth = month(DATEADD(MONTH, number, @begin))
FROM master..spt_values
WHERE type = 'p'
ORDER BY number
DECLARE @d1 DATETIME,@d2 DATETIME
SELECT @d1='2012-08-15',@d2='2013-01-10'
;
WITH a1 AS
(
SELECT TOP (DATEDIFF(mm,@d1,@d2)+1)
DATEADD(mm,ROW_NUMBER() OVER (ORDER BY GETDATE())-1,@d1) date_d
FROM master.dbo.spt_values
)
SELECT convert(char(10),DATEADD(mm, DATEDIFF(mm,0,date_d), 0),20) startDate,
DATEPART(yy,date_d) yy,DATEPART(mm,date_d) mm
FROM a1
DECLARE @begin DATETIME, @end DATETIME
SELECT @begin = '2012-08-15', @end = '2013-01-10'
SELECT TOP(DATEDIFF(MONTH, @begin, @end)+1)
themonth = CONVERT(CHAR(10), DATEADD(MONTH, number, CONVERT(CHAR(8), @begin, 120) + '01'), 120)
FROM master..spt_values
WHERE type = 'p'
ORDER BY number
SELECT TOP(DATEDIFF(MONTH, @begin, @end)+1)
theyear = YEAR(DATEADD(MONTH, number, CONVERT(CHAR(8), @begin, 120) + '01')),
themonth = month(DATEADD(MONTH, number, CONVERT(CHAR(8), @begin, 120) + '01'))
FROM master..spt_values
WHERE type = 'p'
ORDER BY number
--弄成存储过程或者函数
create proc p_myDate @beginDate varchar(10),@endDate varchar(10)
as
begin
declare @beginDate1 varchar(10),@endDate1 varchar(10)
set @beginDate1=LEFT(@beginDate,8)+'01'
set @endDate1=LEFT(@endDate,8)+'01'
declare @nowDate dateTime
set @nowDate=CONVERT(datetime,@beginDate1)
declare @jg1 table(jg varchar(10))
while @nowDate<=CONVERT(datetime,@endDate)
begin
insert into @jg1 select CONVERT(varchar(10),@nowDate,120)
set @nowDate=DATEADD(MM,1,@nowDate)
end
select jg as '查询年份' from @jg1
select DATEPART(YY,jg) as '查询年份',DATEPART(MM,jg) as '查询月份' from @jg1
end
go
--执行:set
exec p_myDate @beginDate='2012-08-15',@endDate='2013-01-10'
drop proc p_myDate
--结果:
查询年份
----------
2012-08-01
2012-09-01
2012-10-01
2012-11-01
2012-12-01
2013-01-01
(6 行受影响)
查询年份 查询月份
----------- -----------
2012 8
2012 9
2012 10
2012 11
2012 12
2013 1
(6 行受影响)
declare @beginDate varchar(10),@endDate varchar(10)
set @beginDate='2012-08-15'
set @endDate='2013-01-10'
declare @beginDate1 varchar(10),@endDate1 varchar(10)
set @beginDate1=LEFT(@beginDate,8)+'01'
set @endDate1=LEFT(@endDate,8)+'01'
declare @nowDate dateTime
set @nowDate=CONVERT(datetime,@beginDate1)
declare @jg1 table(jg varchar(10))
while @nowDate<=CONVERT(datetime,@endDate)
begin
insert into @jg1 select CONVERT(varchar(10),@nowDate,120)
set @nowDate=DATEADD(MM,1,@nowDate)
end
select jg as '查询年份' from @jg1
select DATEPART(YY,jg) as '查询年份',DATEPART(MM,jg) as '查询月份' from @jg1
--result:
(1 行受影响)
查询年份
----------
2012-08-01
2012-09-01
2012-10-01
2012-11-01
2012-12-01
2013-01-01
(6 行受影响)
查询年份 查询月份
----------- -----------
2012 8
2012 9
2012 10
2012 11
2012 12
2013 1
(6 行受影响)