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 @BeginDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @N INT
DECLARE @I INT
SET LANGUAGE '简体中文'
CREATE TABLE TB([DATETIME] DATE,[WEEK] NVARCHAR(20))
SELECT DATENAME(WEEKDAY, @BEGINDATE)
SELECT DATENAME(WEEKDAY, @EndDate)
SET @N=DATEDIFF(DAY,@BeginDate, @EndDate)
SET @I=0
IF(@N>0)
BEGIN
 WHILE(@I<=@N)
 BEGIN
INSERT INTO TB([DATETIME] ,[WEEK])
SELECT  DATEADD(DAY,@I, @BeginDate),DATENAME(WEEKDAY, DATEADD(DAY,@I, @BeginDate))
SET @I=@I+1;
 END
END
SELECT * FROM TB
DROP TABLE TB 
根据楼主的例如来的

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 @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 行受影响)*/