如何将月转换为星期！！高分求助！！！需求如下！！！

[xiaodian2003]

我输入一时间段如‘2002-12-1’到’2003-1-5‘
输出结果为
年 星期
2002-12-7 1
2002-12-14 2
2002-12-21 3
2002-12-31 4
2003-1-7 5

[pengdali]

declare @a datetime,@b datetime
select @a = '2002-12-01',@b='2003-12-5'

Select dateadd(week,id,@a) 年,id+1 周数 from (select (select count(*) from sysobjects where id<a.id) id from sysobjects a) bb where dateadd(week,id,@a)<=@b

[txlicenhe]

datepart(weekday,日期)

[txlicenhe]

case datepart(weekday,日期) when 1 then '天' else cast(datepart(weekday,日期)-1 as char(1)) end

[friendliu]

datepart(weekday,date)
DATEPART
返回代表指定日期的指定日期部分的整数。

语法
DATEPART ( datepart , date )

参数
datepart

是指定应返回的日期部分的参数。下表列出了 Microsoft® SQL Server™ 识别的日期部分和缩写。
期部分 缩写
year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, y
day dd, d
week wk, ww
weekday dw
Hour hh
minute mi, n
second ss, s
millisecond ms

[xyhr2003]

datepart(dw,日期)

[pengdali]

declare @a datetime,@b datetime
select @a='2002-12-1',@b='2003-1-5'

select dateadd(day,id,@a) 年,case datepart(weekday,dateadd(day,id,@a)) when 1 then '7' else cast(datepart(weekday,dateadd(day,id,@a))-1 as char(1)) end
from (
select (select count(*) from sysobjects where id<tem.id) id from sysobjects tem) aa where dateadd(day,id,@a)<=@b

[zjcxc]

--楼主给出的星期不对哦,应该是:

--每周的第一天为星期一的情况
select dt,datepart(weekday,dt-1)
from(
select dt=convert(datetime,'2002-12-7')
union all select '2002-12-14'
union all select '2002-12-21'
union all select '2002-12-31'
union all select '2003-1-7'
) a

--每周的第一天为星期日的情况
select dt,datepart(weekday,dt)
from(
select dt=convert(datetime,'2002-12-7')
union all select '2002-12-14'
union all select '2002-12-21'
union all select '2002-12-31'
union all select '2003-1-7'
) a

[zjcxc]

--下面是得到中文的星期
select dt,'星期'+substring('一二三四五六日',datepart(weekday,dt-1),1)
from(
select dt=convert(datetime,'2002-12-7')
union all select '2002-12-14'
union all select '2002-12-21'
union all select '2002-12-31'
union all select '2003-1-7'
) a

[yown]

请查阅：datepart(weekday,日期)

select date111,datepart(week,date111)....

[xiaodian2003]

可能是大家误会我的意思了。周数的输出是替增的。就是统计两个时间段的总共的周数。如果该时间段有40个周。输出的结果应该就是1,2,3,4,.....40
‘2002-12-1’到’2003-12-5‘
年 周数
2002-12-7 1
2002-12-14 2
2002-12-21 3
2002-12-31 4
2003-1-7 5
2003-1-14 6
2003-1-21 7
2003-1-31 8
2003-2-7 9
2003-2-14 10
2003-2-21 11
2003-2-28 12
........ ..
........ ..
以此类推

[txlicenhe]

Select top 40 dateadd(week,id,@beginDate),id
from
(
Select top 100 (select sum(1) from sysobjects where name<= a.name)-1 as id from sysobjects a
) bb

[txlicenhe]

declare @beginDate datetime
set @beginDate = '2002-12-01'
Select top 40 dateadd(week,id,@beginDate)-1,id
from
(
Select top 40 (select sum(1) from sysobjects where name<= a.name) as id from sysobjects a
) bb
id
------------------------------------------------------ -----------
2002-12-07 00:00:00.000 1
2002-12-14 00:00:00.000 2
2002-12-21 00:00:00.000 3
2002-12-28 00:00:00.000 4
2003-01-04 00:00:00.000 5
2003-01-11 00:00:00.000 6
2003-01-18 00:00:00.000 7
2003-01-25 00:00:00.000 8
2003-02-01 00:00:00.000 9
2003-02-08 00:00:00.000 10
2003-02-15 00:00:00.000 11
2003-02-22 00:00:00.000 12
2003-03-01 00:00:00.000 13
2003-03-08 00:00:00.000 14
2003-03-15 00:00:00.000 15
2003-03-22 00:00:00.000 16
2003-03-29 00:00:00.000 17
2003-04-05 00:00:00.000 18
2003-04-12 00:00:00.000 19
2003-04-19 00:00:00.000 20
2003-04-26 00:00:00.000 21
2003-05-03 00:00:00.000 22
2003-05-10 00:00:00.000 23
2003-05-17 00:00:00.000 24
2003-05-24 00:00:00.000 25
2003-05-31 00:00:00.000 26
2003-06-07 00:00:00.000 27
2003-06-14 00:00:00.000 28
2003-06-21 00:00:00.000 29
2003-06-28 00:00:00.000 30
2003-07-05 00:00:00.000 31
2003-07-12 00:00:00.000 32
2003-07-19 00:00:00.000 33
2003-07-26 00:00:00.000 34
2003-08-02 00:00:00.000 35
2003-08-09 00:00:00.000 36
2003-08-16 00:00:00.000 37
2003-08-23 00:00:00.000 38
2003-08-30 00:00:00.000 39
2003-09-06 00:00:00.000 40

（所影响的行数为 40 行）

[txlicenhe]

declare @beginDate datetime
set @beginDate = '2002-12-01'
Select top 40 convert(varchar(10),dateadd(week,id,@beginDate)-1,120),id
from
(
Select top 40 (select sum(1) from sysobjects where name<= a.name) as id from sysobjects a
) bb

id
---------- -----------
2002-12-07 1
2002-12-14 2
2002-12-21 3
2002-12-28 4
2003-01-04 5
2003-01-11 6
2003-01-18 7
2003-01-25 8
2003-02-01 9
2003-02-08 10
2003-02-15 11
2003-02-22 12
2003-03-01 13
2003-03-08 14
2003-03-15 15
2003-03-22 16
2003-03-29 17
2003-04-05 18
2003-04-12 19
2003-04-19 20
2003-04-26 21
2003-05-03 22
2003-05-10 23
2003-05-17 24
2003-05-24 25
2003-05-31 26
2003-06-07 27
2003-06-14 28
2003-06-21 29
2003-06-28 30
2003-07-05 31
2003-07-12 32
2003-07-19 33
2003-07-26 34
2003-08-02 35
2003-08-09 36
2003-08-16 37
2003-08-23 38
2003-08-30 39
2003-09-06 40

（所影响的行数为 40 行）