declare @input datetime set @input='2009-6-16' select dateadd(day, case datepart(weekday, dateadd(year,-1,@input)) when 7 then 3 when 1 then 2 when 2 then 1 when 3 then 0 when 4 then -1 when 5 then -2 when 6 then -3 end, dateadd(year,-1,@input) ) /* 2008-06-17 00:00:00.000 */
declare @date datetime set @date='2009-6-16' select dateadd(day, case datepart(weekday, dateadd(year,-1,@date)) when 7 then 3 when 1 then 2 when 2 then 1 when 3 then 0 when 4 then -1 when 5 then -2 when 6 then -3 end, dateadd(year,-1,@date) )
declare @sql datetime declare @i int set @sql='2008-6-16' set @i=(select datepart(wk,@sql)) select @i select dateadd(day,(@i-1)*7,'2008-1-1')----------------------- 2008-06-17 00:00:00.000(1 行受影响)
修改一下格式select dateadd(d,-abs(datepart(dw,getdate())- datepart(dw,dateadd(yy,-1,getdate()))),dateadd(yy,-1,getdate())) as a, dateadd(d,abs(datepart(dw,getdate())- datepart(dw,dateadd(yy,-1,getdate()))),dateadd(yy,-1,getdate())) as b ---------------------------------------------------- a b 2008-06-15 16:50:16.030 2008-06-17 16:50:16.030
select dateadd(day, case datepart(weekday, dateadd(year,-1,getdate())) when 7 then 3 when 1 then 2 when 2 then 1 when 3 then 0 when 4 then -1 when 5 then -2 when 6 then -3 end, dateadd(year,-1,getdate()))
declare @input datetime set @input='2009-06-16' select dateadd(day, case ( datepart(weekday,@input )- datepart(weekday,dateadd(year,-1,@input) )) when 0 then 0 when 1 then 1 when 2 then 2 when 3 then 3 when 4 then -3 when 5 then -2 when 6 then -1 when -1 then -1 when -2 then -2 when -3 then -3 when -4 then 3 when -5 then 2 when -6 then 1 end, dateadd(year,-1,@input) )
这个是任意一天的情测试 declare @input datetime, @n int set @input='2009-6-16' set @n=(select datepart(dw, @input)) select dateadd(day, case (datepart(dw, dateadd(year,-1,@input))-1) when 0 then abs(0-@n+1) when 1 then abs(1-@n+1) when 2 then abs(2-@n+1) when 3 then abs(3-@n+1) when 4 then abs(4-@n+1) when 5 then abs(5-@n+1) when 6 then abs(6-@n+1) end, dateadd(year,-1,@input) ) /*------------- 2008-06-17 00:00:00.000---------*/
set @input='2009-6-16'
select
dateadd(day,
case datepart(weekday, dateadd(year,-1,@input))
when 7 then 3
when 1 then 2
when 2 then 1
when 3 then 0
when 4 then -1
when 5 then -2
when 6 then -3
end,
dateadd(year,-1,@input) )
/*
2008-06-17 00:00:00.000
*/
set @date='2009-6-16'
select
dateadd(day,
case datepart(weekday, dateadd(year,-1,@date))
when 7 then 3
when 1 then 2
when 2 then 1
when 3 then 0
when 4 then -1
when 5 then -2
when 6 then -3
end,
dateadd(year,-1,@date) )
select dateadd(d,-abs(datepart(dw,getdate())- datepart(dw,dateadd(yy,-1,getdate()))),dateadd(yy,-1,getdate())), dateadd(d,abs(datepart(dw,getdate())- datepart(dw,dateadd(yy,-1,getdate()))),dateadd(yy,-1,getdate()))
--------------------------------
2008-06-15 16:48:04.593 2008-06-17 16:48:04.593
declare @i int
set @sql='2008-6-16'
set @i=(select datepart(wk,@sql))
select @i
select dateadd(day,(@i-1)*7,'2008-1-1')-----------------------
2008-06-17 00:00:00.000(1 行受影响)
dateadd(d,abs(datepart(dw,getdate())- datepart(dw,dateadd(yy,-1,getdate()))),dateadd(yy,-1,getdate())) as b
----------------------------------------------------
a b
2008-06-15 16:50:16.030
2008-06-17 16:50:16.030
dateadd(day,
case datepart(weekday, dateadd(year,-1,getdate()))
when 7 then 3
when 1 then 2
when 2 then 1
when 3 then 0
when 4 then -1
when 5 then -2
when 6 then -3
end,
dateadd(year,-1,getdate()))
select dateadd(d,(datepart(wk,dateadd(yy,-1,'2009-06-16'))-1)*7,convert(varchar(5),dateadd(yy,-1,'2009-06-16'),120)+'01-01')
select dateadd(d,2-(datepart(weekday,dateadd(yy,-1,'2009-06-16'))+@@datefirst-2)%7-1,dateadd(yy,-1,'2009-06-16'))
set @input='2009-06-16'
select
dateadd(day,
case ( datepart(weekday,@input )- datepart(weekday,dateadd(year,-1,@input) ))
when 0 then 0
when 1 then 1
when 2 then 2
when 3 then 3
when 4 then -3
when 5 then -2
when 6 then -1
when -1 then -1
when -2 then -2
when -3 then -3
when -4 then 3
when -5 then 2
when -6 then 1
end,
dateadd(year,-1,@input) )
可以在这里看到相同的帖子!!
http://topic.csdn.net/u/20090616/16/67111d0b-053e-4d32-94c2-2babe022ffcf.html?seed=1071885970
declare @input datetime, @n int
set @input='2009-6-16'
set @n=(select datepart(dw, @input))
select
dateadd(day,
case (datepart(dw, dateadd(year,-1,@input))-1)
when 0 then abs(0-@n+1)
when 1 then abs(1-@n+1)
when 2 then abs(2-@n+1)
when 3 then abs(3-@n+1)
when 4 then abs(4-@n+1)
when 5 then abs(5-@n+1)
when 6 then abs(6-@n+1)
end,
dateadd(year,-1,@input) )
/*-------------
2008-06-17 00:00:00.000---------*/