输入条件为 年份和第几周，怎么得出这个年份这个周星期一是什么日期？
如题，
比如输入 2007 1
也就是求2007年第一周的星期一是哪个日期
谢谢
[解决办法]
and datepart(dw,col)=2 --星期1
因为对于dw部分，
sunday=1，monday=2
提供给你一个生成指定起始日期和结尾日期时间结构表的sql，你可以用它生成环境来测试一下。
if exists (select * from dbo.sysobjects where id = object_id(N '[dbo].[时间] ') and OBJECTPROPERTY(id, N 'IsUserTable ') = 1)
drop table [dbo].[时间]
GO
CREATE TABLE [dbo].[时间] (
[time_id] [int] IDENTITY (1, 1) NOT NULL ,
[the_date] [datetime] NULL ,
[the_day] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
[the_month] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
[the_year] [smallint] NULL ,
[day_of_month] [smallint] NULL ,
[week_of_year] [float] NULL ,
[month_of_year] [smallint] NULL ,
[quarter] [nvarchar] (2) COLLATE Chinese_PRC_CI_AS NULL ,
[tenday_of_month] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
[half_year] [nvarchar] (15) COLLATE Chinese_PRC_CI_AS NULL ,
) ON [PRIMARY]
GO
truncate table 时间
go
declare @DateJ int
declare @CurDate datetime
declare @StartDate datetime
declare @EndDate datetime
set @StartDate= '2007-01-01 ' ---------这里填写起始时间
set @EndDate= '2008-12-31 ' ---------这里填写结束时间
--SELECT DATEDIFF(day, @StartDate, @EndDate) into @DateJ
set@CurDate= @StartDate
while @CurDate <=@EndDate BEGIN
insert into 时间 (the_date,the_day,the_month,the_year,day_of_month,
week_of_year,month_of_year,quarter,tenday_of_month,half_year)
values (@CurDate,
case when DATEPART(weekday, @CurDate)=1 then '星期日 '
when DATEPART(weekday, @CurDate)=2 then '星期一 '
when DATEPART(weekday, @CurDate)=3 then '星期二 '
when DATEPART(weekday, @CurDate)=4 then '星期三 '
when DATEPART(weekday, @CurDate)=5 then '星期四 '
when DATEPART(weekday, @CurDate)=6 then '星期五 '
when DATEPART(weekday, @CurDate)=7 then '星期六 '
end ,
case when DATEPART(Month, @CurDate)=1 then '一月 '
when DATEPART(Month, @CurDate)=2 then '二月 '
when DATEPART(Month, @CurDate)=3 then '三月 '
when DATEPART(Month, @CurDate)=4 then '四月 '
when DATEPART(Month, @CurDate)=5 then '五月 '
when DATEPART(Month, @CurDate)=6 then '六月 '
when DATEPART(Month, @CurDate)=7 then '七月 '
when DATEPART(Month, @CurDate)=8 then '八月 '
when DATEPART(Month, @CurDate)=9 then '九月 '
when DATEPART(Month, @CurDate)=10 then '十月 '
when DATEPART(Month, @CurDate)=11 then '十一月 '
when DATEPART(Month, @CurDate)=12 then '十二月 '
end ,
DATENAME(year, @CurDate) ,
DATENAME(day, @CurDate) ,
DATENAME(week, @CurDate),DATENAME(month, @CurDate) ,DATENAME(quarter, @CurDate)
,
case when DATEPART(day, @CurDate) <=10 then '上旬 '
when DATEPART(day, @CurDate) <=20 and DATEPART(day, @CurDate)> 10 then '中旬 '
when DATEPART(day, @CurDate)> 20 then '下旬 '
end,
case when DATEPART(month, @CurDate) <=6 then '上半年 '
when DATEPART(month, @CurDate)> 6 then '下半年 '
end
)
--print @CurDate
set@CurDate=DATEADD(day,1,@CurDate)
end
查询：
select the_date from [时间]
where year(the_date)= '2007 '
and datepart(wk,the_date)=1
and datepart(dw,the_date)=2
结果；
------------
the_date
2007-01-01 00:00:00.000
[解决办法]
declare @year char(4)
declare @week int
set @year= '2007 '
set @week=1
select dateadd(wk,@week-1,@year)-datepart(weekday,dateadd(wk,@week-1,@year))+2
--结果
2007-01-01 00:00:00.000
（所影响的行数为 1 行）
