如何用一个SQL语句，把某月所有的天数列出来。
如：列出2006.7所有的日期：
2006.7.1
2006.7.2
2006.7.3
……
2006.7.31
要求，使用SQL语句,最好是一句语句
如何用一个SQL语句，把某月所有的天数列出来。
各位，有什么好方法吗？
[解决办法]
一句語句比較困難，借用下臨時表
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
*/
[解决办法]
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.000
2006-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
[解决办法]
set nocount on
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 @t
time
----------
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
