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

Question

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

Answer 1

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 '一周的结束'

Answer 2

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

Answer 3

http://microshaoft.cnblogs.com/archive/2005/04/26/145500.htmldeclare @ datetimeset @ = '1995-02-25 11:00:50' 
--set @ = getdate() --set datefirst 3select @ 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 

Answer 4

http://microshaoft.cnblogs.com/archive/2005/04/26/145500.htmldeclare @ datetimeset @ = '1995-02-25 11:00:50' 
--set @ = getdate() --set datefirst 3select @ 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 

Answer 5

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

Answer 6

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

Answer 7

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