一句語句比較困難,借用下臨時表Declare @Year int, @Month Int Select @Year = 2007, @Month = 7 Select TOP 50 ID = Identity(Int, 0, 1) Into #T From SysColumns Select Convert(Varchar(10), DateAdd(dd, ID, Cast(Rtrim(@Year) + '-' + Rtrim(@Month) + '-' + '01' As DateTime)), 120) From #T Where ID < DateDiff(dd, Cast(Rtrim(@Year) + '-' + Rtrim(@Month) + '-' + '01' As DateTime), DateAdd(mm, 1, Cast(Rtrim(@Year) + '-' + Rtrim(@Month) + '-' + '01' As DateTime))) Drop Table #T --Result /* 2007-07-01 2007-07-02 2007-07-03 2007-07-04 2007-07-05 2007-07-06 2007-07-07 2007-07-08 2007-07-09 2007-07-10 2007-07-11 2007-07-12 2007-07-13 2007-07-14 2007-07-15 2007-07-16 2007-07-17 2007-07-18 2007-07-19 2007-07-20 2007-07-21 2007-07-22 2007-07-23 2007-07-24 2007-07-25 2007-07-26 2007-07-27 2007-07-28 2007-07-29 2007-07-30 2007-07-31 */
換種格式Declare @Year int, @Month Int Select @Year = 2007, @Month = 7 Select TOP 50 ID = Identity(Int, 0, 1) Into #T From SysColumns Select Convert(Varchar(10), DateAdd(dd, ID, Cast(Rtrim(@Year) + '-' + Rtrim(@Month) + '-' + '01' As DateTime)), 102) From #T Where ID < DateDiff(dd, Cast(Rtrim(@Year) + '-' + Rtrim(@Month) + '-' + '01' As DateTime), DateAdd(mm, 1, Cast(Rtrim(@Year) + '-' + Rtrim(@Month) + '-' + '01' As DateTime))) Drop Table #T --Result /* 2007.07.01 2007.07.02 2007.07.03 2007.07.04 2007.07.05 2007.07.06 2007.07.07 2007.07.08 2007.07.09 2007.07.10 2007.07.11 2007.07.12 2007.07.13 2007.07.14 2007.07.15 2007.07.16 2007.07.17 2007.07.18 2007.07.19 2007.07.20 2007.07.21 2007.07.22 2007.07.23 2007.07.24 2007.07.25 2007.07.26 2007.07.27 2007.07.28 2007.07.29 2007.07.30 2007.07.31 */
if object_id('tempdb..#tmp') is not null drop table #tmp GO ----创建循环临时表 select top 31 id = identity(int,0,1) into #tmp from syscolumns,sysobjects ----创建测试数据 declare @t table(d varchar(10)) insert @t select '2006.7' union all select '2006.9' ----查询 select dateadd(day,b.id,a.d + '.01') from @t as a inner join #tmp as b on dateadd(day,b.id,a.d + '.01') <= dateadd(day,-1,dateadd(month,1,a.d + '.01')) ----清除测试环境 drop table #tmp/*结果 ------------------------------------------------------ 2006-07-01 00:00:00.000 ...... 2006-07-31 00:00:00.0002006-09-01 00:00:00.000 ...... 2006-09-30 00:00:00.000 */
建一个辅助表create table #tab ( id int ) insert into #tab select 0 insert into #tab select 1 insert into #tab select 2 insert into #tab select 3 insert into #tab select 4 insert into #tab select 5 insert into #tab select 6 insert into #tab select 7 insert into #tab select 8 insert into #tab select 9 insert into #tab select 10 insert into #tab select 11 insert into #tab select 12 insert into #tab select 13 insert into #tab select 14 insert into #tab select 15 insert into #tab select 16 insert into #tab select 17 insert into #tab select 18 insert into #tab select 19 insert into #tab select 20 insert into #tab select 21 insert into #tab select 22 insert into #tab select 23 insert into #tab select 24 insert into #tab select 25 insert into #tab select 26 insert into #tab select 27 insert into #tab select 28 insert into #tab select 29 insert into #tab select 30 insert into #tab select 31--语句 declare @mydate varchar(6) set @mydate = '200606' select dateadd(day,id,stuff(@mydate,5,0,'-')+'-01') as dat from #tab where id < datediff(day,stuff(@mydate,5,0,'-')+'-01',dateadd(mm,1,stuff(@mydate,5,0,'-')+'-01')) order by dat
--建立一辅助日期表 create table dt ( dtcol varchar(2) ) insert into dt select '01' insert into dt select '02' insert into dt select '03' insert into dt select '04' insert into dt select '05' insert into dt select '06' insert into dt select '07' insert into dt select '08' insert into dt select '09' insert into dt select '10' insert into dt select '11' insert into dt select '12' insert into dt select '13' insert into dt select '14' insert into dt select '15' insert into dt select '16' insert into dt select '17' insert into dt select '18' insert into dt select '19' insert into dt select '20' insert into dt select '21' insert into dt select '22' insert into dt select '23' insert into dt select '24' insert into dt select '25' insert into dt select '26' insert into dt select '27' insert into dt select '28' insert into dt select '29' insert into dt select '30' insert into dt select '31' go select ('2007-07-'+dtcol) dtime from dt where '2007-07-'+dtcol<=dateadd(day,-1,'2007-'+cast (cast('7' as int )+1 as varchar) +'-1') --结果 dtime ---------- 2007-07-01 2007-07-02 2007-07-03 2007-07-04 2007-07-05 2007-07-06 2007-07-07 2007-07-08 2007-07-09 2007-07-10 2007-07-11 2007-07-12 2007-07-13 2007-07-14 2007-07-15 2007-07-16 2007-07-17 2007-07-18 2007-07-19 2007-07-20 2007-07-21 2007-07-22 2007-07-23 2007-07-24 2007-07-25 2007-07-26 2007-07-27 2007-07-28 2007-07-29 2007-07-30 2007-07-31
Select @Year = 2007, @Month = 7
Select TOP 50 ID = Identity(Int, 0, 1) Into #T From SysColumns
Select Convert(Varchar(10), DateAdd(dd, ID, Cast(Rtrim(@Year) + '-' + Rtrim(@Month) + '-' + '01' As DateTime)), 120) From #T
Where ID < DateDiff(dd, Cast(Rtrim(@Year) + '-' + Rtrim(@Month) + '-' + '01' As DateTime), DateAdd(mm, 1, Cast(Rtrim(@Year) + '-' + Rtrim(@Month) + '-' + '01' As DateTime)))
Drop Table #T
--Result
/*
2007-07-01
2007-07-02
2007-07-03
2007-07-04
2007-07-05
2007-07-06
2007-07-07
2007-07-08
2007-07-09
2007-07-10
2007-07-11
2007-07-12
2007-07-13
2007-07-14
2007-07-15
2007-07-16
2007-07-17
2007-07-18
2007-07-19
2007-07-20
2007-07-21
2007-07-22
2007-07-23
2007-07-24
2007-07-25
2007-07-26
2007-07-27
2007-07-28
2007-07-29
2007-07-30
2007-07-31
*/
Select @Year = 2007, @Month = 7
Select TOP 50 ID = Identity(Int, 0, 1) Into #T From SysColumns
Select Convert(Varchar(10), DateAdd(dd, ID, Cast(Rtrim(@Year) + '-' + Rtrim(@Month) + '-' + '01' As DateTime)), 102) From #T
Where ID < DateDiff(dd, Cast(Rtrim(@Year) + '-' + Rtrim(@Month) + '-' + '01' As DateTime), DateAdd(mm, 1, Cast(Rtrim(@Year) + '-' + Rtrim(@Month) + '-' + '01' As DateTime)))
Drop Table #T
--Result
/*
2007.07.01
2007.07.02
2007.07.03
2007.07.04
2007.07.05
2007.07.06
2007.07.07
2007.07.08
2007.07.09
2007.07.10
2007.07.11
2007.07.12
2007.07.13
2007.07.14
2007.07.15
2007.07.16
2007.07.17
2007.07.18
2007.07.19
2007.07.20
2007.07.21
2007.07.22
2007.07.23
2007.07.24
2007.07.25
2007.07.26
2007.07.27
2007.07.28
2007.07.29
2007.07.30
2007.07.31
*/
drop table #tmp
GO
----创建循环临时表
select top 31 id = identity(int,0,1) into #tmp from syscolumns,sysobjects
----创建测试数据
declare @t table(d varchar(10))
insert @t
select '2006.7' union all
select '2006.9'
----查询
select dateadd(day,b.id,a.d + '.01') from @t as a
inner join #tmp as b on dateadd(day,b.id,a.d + '.01') <= dateadd(day,-1,dateadd(month,1,a.d + '.01'))
----清除测试环境
drop table #tmp/*结果
------------------------------------------------------
2006-07-01 00:00:00.000
......
2006-07-31 00:00:00.0002006-09-01 00:00:00.000
......
2006-09-30 00:00:00.000
*/
(
id int
)
insert into #tab select 0
insert into #tab select 1
insert into #tab select 2
insert into #tab select 3
insert into #tab select 4
insert into #tab select 5
insert into #tab select 6
insert into #tab select 7
insert into #tab select 8
insert into #tab select 9
insert into #tab select 10
insert into #tab select 11
insert into #tab select 12
insert into #tab select 13
insert into #tab select 14
insert into #tab select 15
insert into #tab select 16
insert into #tab select 17
insert into #tab select 18
insert into #tab select 19
insert into #tab select 20
insert into #tab select 21
insert into #tab select 22
insert into #tab select 23
insert into #tab select 24
insert into #tab select 25
insert into #tab select 26
insert into #tab select 27
insert into #tab select 28
insert into #tab select 29
insert into #tab select 30
insert into #tab select 31--语句
declare @mydate varchar(6)
set @mydate = '200606'
select dateadd(day,id,stuff(@mydate,5,0,'-')+'-01') as dat
from #tab
where id < datediff(day,stuff(@mydate,5,0,'-')+'-01',dateadd(mm,1,stuff(@mydate,5,0,'-')+'-01'))
order by dat
create table dt
(
dtcol varchar(2)
)
insert into dt select '01'
insert into dt select '02'
insert into dt select '03'
insert into dt select '04'
insert into dt select '05'
insert into dt select '06'
insert into dt select '07'
insert into dt select '08'
insert into dt select '09'
insert into dt select '10'
insert into dt select '11'
insert into dt select '12'
insert into dt select '13'
insert into dt select '14'
insert into dt select '15'
insert into dt select '16'
insert into dt select '17'
insert into dt select '18'
insert into dt select '19'
insert into dt select '20'
insert into dt select '21'
insert into dt select '22'
insert into dt select '23'
insert into dt select '24'
insert into dt select '25'
insert into dt select '26'
insert into dt select '27'
insert into dt select '28'
insert into dt select '29'
insert into dt select '30'
insert into dt select '31'
go
select ('2007-07-'+dtcol) dtime from dt where '2007-07-'+dtcol<=dateadd(day,-1,'2007-'+cast (cast('7' as int )+1 as varchar) +'-1')
--结果
dtime
----------
2007-07-01
2007-07-02
2007-07-03
2007-07-04
2007-07-05
2007-07-06
2007-07-07
2007-07-08
2007-07-09
2007-07-10
2007-07-11
2007-07-12
2007-07-13
2007-07-14
2007-07-15
2007-07-16
2007-07-17
2007-07-18
2007-07-19
2007-07-20
2007-07-21
2007-07-22
2007-07-23
2007-07-24
2007-07-25
2007-07-26
2007-07-27
2007-07-28
2007-07-29
2007-07-30
2007-07-31
declare @dtBegin datetime
declare @dtEnd datetime
declare @t table(dt datetime)select @dtBegin='2006-7-1'
select @dtEnd=dateadd(day,
-1,
convert(char(8),dateadd(month,1,@dtBegin),120)+'01'
)while @dtBegin<=@dtEnd
begin
insert @t select @dtBegin
set @dtBegin=@dtBegin+1
end
select time=convert(char(10),dt,120) from @ttime
----------
2006-07-01
2006-07-02
2006-07-03
2006-07-04
2006-07-05
2006-07-06
2006-07-07
2006-07-08
2006-07-09
2006-07-10
2006-07-11
2006-07-12
2006-07-13
2006-07-14
2006-07-15
2006-07-16
2006-07-17
2006-07-18
2006-07-19
2006-07-20
2006-07-21
2006-07-22
2006-07-23
2006-07-24
2006-07-25
2006-07-26
2006-07-27
2006-07-28
2006-07-29
2006-07-30
2006-07-31