SQL关于日期用法

Question

请教各位高人个问题。我想根据一个时间段，列出时间段内每一天的日期（日期+星期）例如：@begintime='2011-9-1'
      @endtime='2011-9-30'我想通过上面的选择列出下面结果2011-9-1 2011-9-2 2011-9-3 2011-9-4 2011-9-5 2011-9-6 2011-9-7  。。等等
 星期四   星期五   星期六   星期日   星期一   星期二   星期三   。。等等

Answer 1

declare @sql varchar(max),@startdate datetime,@enddate datetime 
set @startdate='2009-09-28'
set @enddate='2009-10-5'
set @sql='select '
select @sql=@sql+','''+convert(varchar(10),dateadd(day,number,@startdate),120)+''''
from master..spt_values where type='P' and number<=datediff(dd,@startdate,@enddate)
select @sql=stuff(@sql,8,1,' ')
exec(@sql)
/*---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
2009-09-28 2009-09-29 2009-09-30 2009-10-01 2009-10-02 2009-10-03 2009-10-04 2009-10-05(1 行受影响)*/

Answer 2

--sql 2000
declare @sdate datetime
declare @edate datetime
set @sdate = '2011-09-01'
set @edate = '2011-09-30'
select 
    日期 = dateadd(dd,num,@sdate) ,星期 = datename(weekday , dateadd(dd,num,@sdate))
from 
    (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a
where
    dateadd(dd,num,@sdate)<=@edate
order by 日期
/*
日期                                                     星期                             
------------------------------------------------------ ------------------------------ 
2011-09-01 00:00:00.000                                星期四
2011-09-02 00:00:00.000                                星期五
2011-09-03 00:00:00.000                                星期六
2011-09-04 00:00:00.000                                星期日
2011-09-05 00:00:00.000                                星期一
2011-09-06 00:00:00.000                                星期二
2011-09-07 00:00:00.000                                星期三
2011-09-08 00:00:00.000                                星期四
2011-09-09 00:00:00.000                                星期五
2011-09-10 00:00:00.000                                星期六
2011-09-11 00:00:00.000                                星期日
2011-09-12 00:00:00.000                                星期一
2011-09-13 00:00:00.000                                星期二
2011-09-14 00:00:00.000                                星期三
2011-09-15 00:00:00.000                                星期四
2011-09-16 00:00:00.000                                星期五
2011-09-17 00:00:00.000                                星期六
2011-09-18 00:00:00.000                                星期日
2011-09-19 00:00:00.000                                星期一
2011-09-20 00:00:00.000                                星期二
2011-09-21 00:00:00.000                                星期三
2011-09-22 00:00:00.000                                星期四
2011-09-23 00:00:00.000                                星期五
2011-09-24 00:00:00.000                                星期六
2011-09-25 00:00:00.000                                星期日
2011-09-26 00:00:00.000                                星期一
2011-09-27 00:00:00.000                                星期二
2011-09-28 00:00:00.000                                星期三
2011-09-29 00:00:00.000                                星期四
2011-09-30 00:00:00.000                                星期五（所影响的行数为 30 行）*/

Answer 3

--sql 2005
declare @startDate datetime
declare @endDate datetimeSELECT @startDate = '2011-09-01' ,@endDate = '2011-09-30'
;WITH tb AS (
SELECT @startDate AS 'date'
UNION ALL
SELECT DATEADD(DAY,1,date) FROM tb WHERE DATE<@endDate
)
SELECT tb.date , datename(weekday,tb.date)from tb/*
date                    
----------------------- ------------------------------
2011-09-01 00:00:00.000 星期四
2011-09-02 00:00:00.000 星期五
2011-09-03 00:00:00.000 星期六
2011-09-04 00:00:00.000 星期日
2011-09-05 00:00:00.000 星期一
2011-09-06 00:00:00.000 星期二
2011-09-07 00:00:00.000 星期三
2011-09-08 00:00:00.000 星期四
2011-09-09 00:00:00.000 星期五
2011-09-10 00:00:00.000 星期六
2011-09-11 00:00:00.000 星期日
2011-09-12 00:00:00.000 星期一
2011-09-13 00:00:00.000 星期二
2011-09-14 00:00:00.000 星期三
2011-09-15 00:00:00.000 星期四
2011-09-16 00:00:00.000 星期五
2011-09-17 00:00:00.000 星期六
2011-09-18 00:00:00.000 星期日
2011-09-19 00:00:00.000 星期一
2011-09-20 00:00:00.000 星期二
2011-09-21 00:00:00.000 星期三
2011-09-22 00:00:00.000 星期四
2011-09-23 00:00:00.000 星期五
2011-09-24 00:00:00.000 星期六
2011-09-25 00:00:00.000 星期日
2011-09-26 00:00:00.000 星期一
2011-09-27 00:00:00.000 星期二
2011-09-28 00:00:00.000 星期三
2011-09-29 00:00:00.000 星期四
2011-09-30 00:00:00.000 星期五(30 行受影响)
*/

Answer 4

DECLARE @start_date VARCHAR(20) ,
    @end_date VARCHAR(20)
SET @start_date = '2011-09-20' 
SET @end_date = '2011-09-30'
SELECT  CONVERT(VARCHAR, DATEADD(dd, number, @start_date), 111) AS date ,
        DATENAME(weekday, DATEADD(dd, number, @start_date)) AS name 
FROM    master..spt_values
WHERE   type = 'p'
        AND number <= DATEDIFF(dd, @start_date, @end_date)

Answer 5

弱弱的问一句，WITH tb 这个表怎么将查询出来的那些日期插入到#temp呢。