sql 日期问题从周转换到日期

[zsx12]

我现在知道 xx年 第x周 可以用什么方法算出 这一周的第一天和最后一天的日期，用sql实现

[子陌红尘]

declare @date datetime,@sdate datetime,@edate datetime
set @date = '2005-01-01'

select @date = dateadd(wk,31,@date)
select @sdate = dateadd(day,7-datepart(weekday,@date)-7,@date),
@edate = dateadd(day,7-datepart(weekday,@date),@date)

select [周一]=@sdate,[周日]=@edate

[tangqijun199]

declare @str varchar(10)
select @str=datename(Week,dateadd(week,14,'2005-01-01'))
select case @str when '星期日' then dateadd(day,-1,dateadd(week,14,'2005-01-01'))
when '星期一' then dateadd(day,-1,dateadd(week,14,'2005-01-01'))
when '星期二' then dateadd(day,-2,dateadd(week,14,'2005-01-01'))
when '星期三' then dateadd(day,-3,dateadd(week,14,'2005-01-01'))
when '星期四' then dateadd(day,-4,dateadd(week,14,'2005-01-01'))
when '星期五' then dateadd(day,-5,dateadd(week,14,'2005-01-01'))
when '星期六' then dateadd(day,-6,dateadd(week,14,'2005-01-01')) end as '一周的开始',
case @str when '星期日' then dateadd(day,6,dateadd(week,14,'2005-01-01'))
when '星期一' then dateadd(day,5,dateadd(week,14,'2005-01-01'))
when '星期二' then dateadd(day,4,dateadd(week,14,'2005-01-01'))
when '星期三' then dateadd(day,3,dateadd(week,14,'2005-01-01'))
when '星期四' then dateadd(day,2,dateadd(week,14,'2005-01-01'))
when '星期五' then dateadd(day,1,dateadd(week,14,'2005-01-01'))
when '星期六' then dateadd(day,0,dateadd(week,14,'2005-01-01')) end as '一周的结束'

[mschen]

--本周第一天
select dateadd(day,2-datepart(dw,getdate()),getdate())

--本周最后一天
select dateadd(day,8-datepart(dw,getdate()),getdate())

[playyuer]

http://microshaoft.cnblogs.com/archive/2005/04/26/145500.html

declare @ datetime

set @ = '1995-02-25 11:00:50'
--set @ = getdate()

--set datefirst 3

select @ as Date
,dateadd(year,datediff(year,0,@),0) as FirstDayOfYear
,dateadd(year,1+datediff(year,0,@),0)-1 as LastDayOfYear
,dateadd(quarter,datediff(quarter,0,@),0) as FirstDayOfQuarter
,dateadd(quarter,1+datediff(quarter,0,@),0)-1 as LastDayOfQuarter
,dateadd(month,datediff(month,0,@),0) as FirstDayOfMonth
,dateadd(month,1+datediff(month,0,@),0)-1 as LastDayOfMonth
,dateadd(week,datediff(week,0,@),0) as FirstDayOfWeek
,dateadd(week,1+datediff(week,0,@),0)-1 as LastDayOfWeek

[playyuer]

http://microshaoft.cnblogs.com/archive/2005/04/26/145500.html

declare @ datetime

set @ = '1995-02-25 11:00:50'
--set @ = getdate()

--set datefirst 3

select @ as Date
,dateadd(year,datediff(year,0,@),0) as FirstDayOfYear
,dateadd(year,1+datediff(year,0,@),0)-1 as LastDayOfYear
,dateadd(quarter,datediff(quarter,0,@),0) as FirstDayOfQuarter
,dateadd(quarter,1+datediff(quarter,0,@),0)-1 as LastDayOfQuarter
,dateadd(month,datediff(month,0,@),0) as FirstDayOfMonth
,dateadd(month,1+datediff(month,0,@),0)-1 as LastDayOfMonth
,dateadd(week,datediff(week,0,@),0) as FirstDayOfWeek
,dateadd(week,1+datediff(week,0,@),0)-1 as LastDayOfWeek

[vivianfdlpw]

alter procedure p_date
@year int=2005, --年份
@week int=33, --第几周
@firstday datetime =null output, --此周的第一天
@endday datetime =null output --此周的最后一天
as
declare @currentWeek int,@currentFirstday datetime
select @currentWeek=datepart(week,getdate()),
@currentFirstday=dateadd(day,1-datepart(weekday,getdate()),getdate())
select @firstday=dateadd(day,7*(@week-@currentWeek),@currentFirstday),
@endday=dateadd(day,7,@firstday)
--select @firstday,@endday
go

[vivianfdlpw]

alter procedure p_date
@year int=2005, --年份
@week int=33, --第几周
@firstday datetime =null output, --此周的第一天
@endday datetime =null output --此周的最后一天
as
declare @currentWeek int,@currentFirstday datetime
select @currentWeek=datepart(week,getdate()),
@currentFirstday=dateadd(day,1-datepart(weekday,getdate()),getdate())
select @firstday=dateadd(day,7*(@week-@currentWeek),@currentFirstday),
@endday=dateadd(day,7,@firstday)
--select @firstday,@endday
go

[vivianfdlpw]

alter procedure p_date
@year int=2005, --年份
@week int=33, --第几周
@firstday datetime =null output, --此周的第一天
@endday datetime =null output --此周的最后一天
as
declare @currentWeek int,@currentFirstday datetime
select @currentWeek=datepart(week,getdate()),
@currentFirstday=dateadd(day,1-datepart(weekday,getdate()),getdate())
select @firstday=dateadd(day,7*(@week-@currentWeek),@currentFirstday),
@endday=dateadd(day,7,@firstday)
--select @firstday,@endday
go