再改一下
declare @dt datetime,@dt2 int
set @dt='2006-03-16'
set @dt2= cast(convert(datetime,cast(year(@dt)-1 as varchar(20))+'-'+cast(month(@dt) as varchar(20))+'-01',120) as int)
print @dt2
print cast(datepart(wk,@dt) as int)%4
set @dt2=@dt2+(7-datepart(weekday,convert(datetime,cast(year(@dt)-1 as varchar(20))+'-'+cast(month(@dt) as varchar(10))+'-1',120)))
+case when cast(datepart(wk,@dt) as int)%4=1 then 0 when cast(datepart(wk,@dt) as int)%4=2 then datepart(weekday,@dt)
when cast(datepart(wk,@dt) as int)%4=3 then
datepart(weekday,@dt)+7 when cast(datepart(wk,@dt) as int)%4=4 then datepart(weekday,@dt)+14 end
print @dt2
select cast(@dt2 as datetime)
declare @selyear varchar(4) --要查的年份
declare @selmonth varchar(2) --要查的月份
declare @selweek int --要查第几周
declare @selweekday int --要查星期几
declare @day int
declare @sel datetime
declare @week int
--输入条件
set @selyear='2006' --年份
set @selmonth='02' --月份
set @selweek=4 --第几周
set @selweekday=7 --星期几
--查询
set @day=0
set @sel=@selyear + '-'+@selmonth +'-'+'01'
print @sel
if datepart(weekday,@sel)=1
begin
set @day=@day+1
end
else
begin
set @day=9-datepart(weekday,@sel)
end
set @week=1
set @day=@day+(@selweek-@week-1)*7+@selweekday
select @selyear +'-'+ @selmonth +'-'+cast(@day as varchar(2))
declare @dt datetime,@dt2 int
set @dt='2006-01-19'
set @dt2= cast(convert(datetime,cast(year(@dt)-1 as varchar(20))+'-'+cast(month(@dt) as varchar(10))+'-1',120) as int)
set @dt2= @dt2+7*(datepart(wk,@dt)-2)+datepart(weekday,@dt)+
(7-datepart(weekday,convert(datetime,cast(year(@dt)-1 as varchar(20))+'-'+cast(month(@dt) as varchar(10))+'-1',120)))
select cast(@dt2 as datetime)
select dateadd(d,((datepart(weekday,@t)+@@datefirst-2)%7+1)-
(datepart(weekday,dateadd(week,
datediff(d,
dateadd(d,7-(datepart(weekday,convert(char(8),@t,120)+'01')+
@@datefirst-2)%7-1,convert(char(8),@t,120)+'01'),
@t)/7+
case when datediff(d,
dateadd(d,7-(datepart(weekday,convert(char(8),@t,120)+'01')+
@@datefirst-2)%7-1,convert(char(8),@t,120)+'01'),
@t)%7>0 then 1 else 0 end
,convert(char(8),dateadd(year,-1,@t),120)+'01'))+@@datefirst-2)%7-1,
dateadd(week,
datediff(d,
dateadd(d,7-(datepart(weekday,convert(char(8),@t,120)+'01')+
@@datefirst-2)%7-1,convert(char(8),@t,120)+'01'),
@t)/7+
case when datediff(d,
dateadd(d,7-(datepart(weekday,convert(char(8),@t,120)+'01')+
@@datefirst-2)%7-1,convert(char(8),@t,120)+'01'),
@t)%7>0 then 1 else 0 end
,convert(char(8),dateadd(year,-1,@t),120)+'01'))
declare @dt datetime,@dt2 int
set @dt='2006-01-19'
set @dt2= cast(convert(datetime,cast(year(@dt)-1 as varchar(20))+'-'+cast(month(@dt) as varchar(10))+'-1',120) as int)
set @dt2= @dt2+7*month(@dt)+datepart(weekday,@dt)
select cast(@dt2 as datetime)