如何选某月所有的日期

Question

如何选某月所有的日期 比如 
2008-06-01
2008-06-02
2008-06-03
2008-06-04
......
2008-06-30

Answer 1

05:
with T
as
(select cast('2008-06-01' as datetime) as date 
union all
select date+1 from T where date+1<'2008-07-01')
select * from t

Answer 2

select * from  表1 where 日期>='2008-06-01' and 日期 <='2008-06-30'

Answer 3

declare @tb table(dt nvarchar(10))
declare @mth int,@yr int
declare @date datetime
set @mth = 6
set @yr =2008
set @date = convert(nvarchar(4),@yr) + '-' +'00'+right(@mth,2) +'-01'
while (month(@date)<@mth+1)
begin
   insert into @tb 
   select convert(nvarchar(10),@date,120) 
   set @date = dateadd(day,1,@date)
end
select * from @tb
/*
dt
----------
2008-06-01
2008-06-02
2008-06-03
2008-06-04
2008-06-05
2008-06-06
2008-06-07
2008-06-08
2008-06-09
2008-06-10
2008-06-11
2008-06-12
2008-06-13
2008-06-14
2008-06-15
2008-06-16
2008-06-17
2008-06-18
2008-06-19
2008-06-20
2008-06-21
2008-06-22
2008-06-23
2008-06-24
2008-06-25
2008-06-26
2008-06-27
2008-06-28
2008-06-29
2008-06-30(30 row(s) affected)
*/

Answer 4

set nocount on 
declare @dt varchar(10),@judge datetime
declare @tb table(dt varchar(10))
set @dt=convert(char(8),getdate(),120)+'01'
set @judge=dateadd(month,1,@dt)
while @dt<@judge
begin
insert into @tb select @dt
set @dt=convert(varchar(10),dateadd(day,1,@dt),120)
end
select * from @tb
set nocount offdt
2008-06-01
2008-06-02
2008-06-03
2008-06-04
2008-06-05
2008-06-06
2008-06-07
2008-06-08
2008-06-09
2008-06-10
2008-06-11
2008-06-12
2008-06-13
2008-06-14
2008-06-15
2008-06-16
2008-06-17
2008-06-18
2008-06-19
2008-06-20
2008-06-21
2008-06-22
2008-06-23
2008-06-24
2008-06-25
2008-06-26
2008-06-27
2008-06-28
2008-06-29
2008-06-30

Answer 5

declare @tb table(dt nvarchar(10))
declare @mth int,@yr int
declare @date datetime
set @mth = 6
set @yr =2008
set @date = convert(nvarchar(4),@yr) + '-'+right('00'+convert(nvarchar(2),@mth),2) +'-01' --这一行
while (month(@date)<@mth+1)
begin
   insert into @tb 
   select convert(nvarchar(10),@date,120) 
   set @date = dateadd(day,1,@date)
end
select * from @tb
前面个有点小问题~

Answer 6

declare @year int, @month int
select @year=2008, @month=7select dateadd(day, colid-1, ltrim(@year*100+@month)+'01') as date
from syscolumns
where id=object_id('syscolumns') and colid<=day(dateadd(month,1,ltrim(@year*100+@month)+'01')-1)
order by colid/*
date                                                   
-----------------------
2008-07-01 00:00:00.000
2008-07-02 00:00:00.000
2008-07-03 00:00:00.000
2008-07-04 00:00:00.000
2008-07-05 00:00:00.000
2008-07-06 00:00:00.000
2008-07-07 00:00:00.000
2008-07-08 00:00:00.000
2008-07-09 00:00:00.000
2008-07-10 00:00:00.000
2008-07-11 00:00:00.000
2008-07-12 00:00:00.000
2008-07-13 00:00:00.000
2008-07-14 00:00:00.000
2008-07-15 00:00:00.000
2008-07-16 00:00:00.000
2008-07-17 00:00:00.000
2008-07-18 00:00:00.000
2008-07-19 00:00:00.000
2008-07-20 00:00:00.000
2008-07-21 00:00:00.000
2008-07-22 00:00:00.000
2008-07-23 00:00:00.000
2008-07-24 00:00:00.000
2008-07-25 00:00:00.000
2008-07-26 00:00:00.000
2008-07-27 00:00:00.000
2008-07-28 00:00:00.000
2008-07-29 00:00:00.000
2008-07-30 00:00:00.000
2008-07-31 00:00:00.000
*/

Answer 7

declare @tb table(dt nvarchar(10))
declare @m varchar(2),@y varchar(4)
declare @d datetime
set @m = '6' --06
set @y ='2008' --8,08,008
set @d = @y+'-'+@m+'-01'
while (month(@d)<@m+1)
begin
   insert into @tb 
   select convert(varchar(10),@d,120) 
   set @d = dateadd(day,1,@d)
end
select * from @tb较少的避免类型转换