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 = getdate() declare @weekday int set @weekday = datepart(weekday,@date)declare @i int set @i = 1 while @i<>0 begin if @weekday = datepart(weekday,dateadd(year,@i,@date)) begin print dateadd(year,@i,@date) set @i = -1 end if @weekday = datepart(weekday,dateadd(year,-@i,@date)) begin print dateadd(year,-@i,@date) set @i = -1 end set @i = @i +1 end--结果 06 16 2015 4:42PM
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 行受影响)
declare @i int,@j int set @i=2while datepart(weekday,dateadd(yy,-@i,'2008-6-16'))<>datepart(weekday,'2009-6-16') begin select @i=@i+1 endset @j=-1while datepart(weekday,dateadd(yy,@j,'2008-6-16'))<>datepart(weekday,'2009-6-16') begin select @j=@j-1 endselect [最近日期]=(case when @i+@j>0 then convert( varchar(10),dateadd(yy,@j,'2008-6-16'),120) when @i+@j<0 then convert( varchar(10),dateadd(yy,@i,'2008-6-16'),120) else convert( varchar(10),dateadd(yy,@i,'2008-6-16'),120)+'和' +convert( varchar(10),dateadd(yy,@j,'2008-6-16'),120) end)最近日期 ---------------------- 2018-06-16和1998-06-16(1 行受影响)
基本上,是可以直接用這個 select dateadd(week,-52,@input)
SET DATEFIRST 1 DECLARE @DATE SMALLDATETIME SET @DATE='2009-6-16'SELECT TOP 1 DATEADD(DAY,D,DATEADD(YEAR,-1,@DATE)) FROM ( SELECT D=1 UNION ALL SELECT D=2 UNION ALL SELECT D=3 UNION ALL SELECT D=4 UNION ALL SELECT D=5 UNION ALL SELECT D=6 UNION ALL SELECT D=-1 UNION ALL SELECT D=-2 UNION ALL SELECT D=-3 UNION ALL SELECT D=-4 UNION ALL SELECT D=-5 UNION ALL SELECT D=-6 ) D WHERE DATEPART(DW,DATEADD(DAY,D,DATEADD(YEAR,-1,@DATE)))=2 ORDER BY ABS(DATEDIFF(DAY,DATEADD(YEAR,-1,@DATE),DATEADD(DAY,D,DATEADD(YEAR,-1,@DATE)))) /* 2008-06-17 00:00:00 */
select dateadd(day,( case datepart(weekday,getdate() ) when 1 then 7 else datepart(weekday,getdate() )+1 end )- ( case datepart(weekday,dateadd(year,-1,getdate()) ) when 1 then 7 else datepart(weekday,dateadd(year,-1,getdate()) )+1 end ),dateadd(year,-1,getdate()))
如果是星期一,then的地方就不是那些数字了 when 7 then 2 when 1 then 1 when 2 then 0 when 3 then -1 when 4 then -2 when 5 then -3 when 6 then 3 end要考量的
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---------*/
修改下 declare @input datetime, @n int set @input='2009-06-16' set @n=(select datepart(dw, @input)) select dateadd(day, case (datepart(dw, dateadd(year,-1,@input))-1) when 0 then @n-1-7 when 1 then @n-1-1 when 2 then @n-1-2 when 3 then @n-1-3 when 4 then @n-1-4 when 5 then @n-1-5 when 6 then @n-1-6 end, dateadd(year,-1,@input) )
这个经过测试是OK的。declare @input datetime set @input='2009-6-15' select dateadd(day, case datepart(weekday, dateadd(year,-1,@input)) when 7 then datepart(weekday,@input) -7 when 1 then datepart(weekday,@input) - 1 when 2 then datepart(weekday,@input) - 2 when 3 then datepart(weekday,@input) - 3 when 4 then datepart(weekday,@input) - 4 when 5 then datepart(weekday,@input) - 5 when 6 then datepart(weekday,@input) - 6 end, dateadd(year,-1,@input) )结果 2008-06-16
又改了下。这次绝对OK declare @input datetime,@TWK int,@LWK int select @input = '2009-6-16' select @TWK = datepart(weekday,@input),@LWK = datepart(weekday, dateadd(year,-1,@input))SELECT DATEADD(DAY, CASE WHEN @LWK - @TWK > 3 THEN 7-(@LWK - @TWK) WHEN @LWK - @TWK <=3 THEN @TWK - @LWK END, dateadd(year,-1,@input))
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 */ select datepart(weekday,'2008-06-17') as 周几 运行结果是: 周几 3
楼主是想用sql实现,还是程序实现啊???
declare @dtnow datetime set @dtnow = getdate() declare @dtlast datetime set @dtlast = dateadd(year,-1,@dtnow)declare @diff int if(abs(datepart(dw,@dtnow) - datepart(dw,@dtlast)) < 4) begin --select datepart(dw,@dtlast) set @diff = abs(datepart(dw,@dtnow) - datepart(dw,@dtlast)) select dateadd(day,@diff,@dtlast) end else begin --select datepart(dw,@dtlast) set @diff = 7 - abs(datepart(dw,@dtnow) + datepart(dw,@dtlast)) select dateadd(day,@diff,@dtlast) end
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
*/
set @date = getdate()
declare @weekday int
set @weekday = datepart(weekday,@date)declare @i int
set @i = 1
while @i<>0
begin
if @weekday = datepart(weekday,dateadd(year,@i,@date))
begin
print dateadd(year,@i,@date)
set @i = -1
end
if @weekday = datepart(weekday,dateadd(year,-@i,@date))
begin
print dateadd(year,-@i,@date)
set @i = -1
end
set @i = @i +1
end--结果
06 16 2015 4:42PM
------------------
2008-06-17 16:43:26.310
--大部份可以簡單的就減52周 (1年)declare @input datetime
set @input='2009-6-16'select dateadd(week,-52,@input)
/*
2008-06-17 00:00:00.000
*/
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 行受影响)
set @i=2while datepart(weekday,dateadd(yy,-@i,'2008-6-16'))<>datepart(weekday,'2009-6-16')
begin
select @i=@i+1
endset @j=-1while datepart(weekday,dateadd(yy,@j,'2008-6-16'))<>datepart(weekday,'2009-6-16')
begin
select @j=@j-1
endselect [最近日期]=(case when @i+@j>0 then convert( varchar(10),dateadd(yy,@j,'2008-6-16'),120)
when @i+@j<0 then convert( varchar(10),dateadd(yy,@i,'2008-6-16'),120)
else convert( varchar(10),dateadd(yy,@i,'2008-6-16'),120)+'和'
+convert( varchar(10),dateadd(yy,@j,'2008-6-16'),120) end)最近日期
----------------------
2018-06-16和1998-06-16(1 行受影响)
基本上,是可以直接用這個
select dateadd(week,-52,@input)
DECLARE @DATE SMALLDATETIME
SET @DATE='2009-6-16'SELECT TOP 1 DATEADD(DAY,D,DATEADD(YEAR,-1,@DATE))
FROM (
SELECT D=1 UNION ALL
SELECT D=2 UNION ALL
SELECT D=3 UNION ALL
SELECT D=4 UNION ALL
SELECT D=5 UNION ALL
SELECT D=6 UNION ALL
SELECT D=-1 UNION ALL
SELECT D=-2 UNION ALL
SELECT D=-3 UNION ALL
SELECT D=-4 UNION ALL
SELECT D=-5 UNION ALL
SELECT D=-6
) D
WHERE DATEPART(DW,DATEADD(DAY,D,DATEADD(YEAR,-1,@DATE)))=2
ORDER BY ABS(DATEDIFF(DAY,DATEADD(YEAR,-1,@DATE),DATEADD(DAY,D,DATEADD(YEAR,-1,@DATE))))
/*
2008-06-17 00:00:00
*/
select dateadd(d,abs(datepart(dw,getdate())- datepart(dw,dateadd(yy,-1,getdate()))),dateadd(yy,-1,getdate()))
如果是星期一,then的地方就不是那些数字了
when 7 then 2
when 1 then 1
when 2 then 0
when 3 then -1
when 4 then -2
when 5 then -3
when 6 then 3
end要考量的
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) )
要先在MSSQL中设置, 一周的开始是星期一
如果一周的开始是星期日, 则要将如下的2修改成1即可
-- 用2009-06-16测试
set datefirst 1
select dateadd(day, 2-datepart(weekday, dateadd(year, -1, '2009-6-16')), '2009-6-16')-- 用2009-06-23测试
set datefirst 1
select dateadd(day, 2-datepart(weekday, dateadd(year, -1, '2009-6-23')), '2009-6-23')-- 变量脚本
declare @dtCur smalldatetime
set @dtCur = '2009-6-16'
set datefirst 1
select dateadd(day, 2-datepart(weekday, dateadd(year, -1, @dtCur)), @dtCur)
家里昨天的sql server 刚被我删了 正在装。。-.-
装好了一定测试
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---------*/
declare @input datetime, @n int
set @input='2009-06-16'
set @n=(select datepart(dw, @input))
select
dateadd(day,
case (datepart(dw, dateadd(year,-1,@input))-1)
when 0 then @n-1-7
when 1 then @n-1-1
when 2 then @n-1-2
when 3 then @n-1-3
when 4 then @n-1-4
when 5 then @n-1-5
when 6 then @n-1-6
end,
dateadd(year,-1,@input) )
这个经过测试是OK的。declare @input datetime
set @input='2009-6-15'
select
dateadd(day,
case datepart(weekday, dateadd(year,-1,@input))
when 7 then datepart(weekday,@input) -7
when 1 then datepart(weekday,@input) - 1
when 2 then datepart(weekday,@input) - 2
when 3 then datepart(weekday,@input) - 3
when 4 then datepart(weekday,@input) - 4
when 5 then datepart(weekday,@input) - 5
when 6 then datepart(weekday,@input) - 6
end,
dateadd(year,-1,@input) )结果
2008-06-16
又改了下。这次绝对OK
declare @input datetime,@TWK int,@LWK int
select @input = '2009-6-16'
select @TWK = datepart(weekday,@input),@LWK = datepart(weekday, dateadd(year,-1,@input))SELECT DATEADD(DAY,
CASE WHEN @LWK - @TWK > 3 THEN 7-(@LWK - @TWK)
WHEN @LWK - @TWK <=3 THEN @TWK - @LWK
END,
dateadd(year,-1,@input))
如果当年是闰年,那么如果你输入的日期A 是 2月29日。又因为这年的上一年没同一个日期。即没有2月29日只有 2月28日。。
得用IF判断下。。 当然 闰年的其他的日期是没问题的咯~
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
*/
select datepart(weekday,'2008-06-17') as 周几
运行结果是:
周几
3
declare @dtlast datetime set @dtlast = dateadd(year,-1,@dtnow)declare @diff int
if(abs(datepart(dw,@dtnow) - datepart(dw,@dtlast)) < 4)
begin
--select datepart(dw,@dtlast)
set @diff = abs(datepart(dw,@dtnow) - datepart(dw,@dtlast))
select dateadd(day,@diff,@dtlast)
end
else
begin
--select datepart(dw,@dtlast)
set @diff = 7 - abs(datepart(dw,@dtnow) + datepart(dw,@dtlast))
select dateadd(day,@diff,@dtlast)
end