获取日期范围中的每一天

--日期
declare @dt1 as datetime 
declare @dt2 as datetime 
set @dt1 = '2011-2-21' 
set @dt2 = '2011-3-10' 

SELECT TOP 8000 id = IDENTITY(int, 0, 1) INTO tmp FROM syscolumns a, syscolumns b  

select 显示 = dateadd(day , id , @dt1) from tmp where dateadd(day , id , @dt1) <= @dt2 

drop table tmp 


----时间

set @dt1 = '11:00:00' 
set @dt2 = '23:00:00' 

SELECT TOP 8000 id = IDENTITY(int, 0, 1) INTO tmp FROM syscolumns a, syscolumns b  

select 显示 = convert(varchar(8),dateadd(hh , id , @dt1),114) from tmp where dateadd(hh , id , @dt1) <= @dt2 

drop table tmp 

 

显示的结果：

--日期

2011-02-21 00:00:00.000
2011-02-22 00:00:00.000
2011-02-23 00:00:00.000
2011-02-24 00:00:00.000
2011-02-25 00:00:00.000
2011-02-26 00:00:00.000
2011-02-27 00:00:00.000
2011-02-28 00:00:00.000
2011-03-01 00:00:00.000
2011-03-02 00:00:00.000
2011-03-03 00:00:00.000
2011-03-04 00:00:00.000
2011-03-05 00:00:00.000
2011-03-06 00:00:00.000
2011-03-07 00:00:00.000
2011-03-08 00:00:00.000
2011-03-09 00:00:00.000
2011-03-10 00:00:00.000

 

--时间

11:00:00
12:00:00
13:00:00
14:00:00
15:00:00
16:00:00
17:00:00
18:00:00
19:00:00
20:00:00
21:00:00
22:00:00
23:00:00

 

 这样写比较优雅

declare @dt1 as datetime 
declare @dt2 as datetime 
set @dt1 = '2011-2-21' 
set @dt2 = '2011-3-10' 
;

with t as
(
select dayss=convert(varchar(10),dateadd(dd,number,@dt1),120)
from master..spt_values
where type='p' and number <= datediff(dd,@dt1,@dt2)
)

select datename(weekday,t.dayss) as 星期,
		t.dayss as 日期 from t