select 订单号,交货期,DATEADD(wk,-datediff(wk,交货期,getdate()),'2005-9-26') as 交货期所属周的周一 from 资料表
--测试环境 declare @t table(订单号 varchar(10), 交货期 datetime) insert into @t select 'a1','2005/9/5' union all select 'a2','2005/9/8' union all select 'a3','2005/9/10' union all select 'a7','2005/9/13' union all select 'a9','2005/9/20' union all select 'a20','2005/9/27' union all select 'a200','2005/9/30' union all select 'a211','2005/10/30' --查询 select 订单号,交货期,交货期所属周的周一=DATEADD(wk, DATEDIFF(wk,0,dateadd(ms,-3,交货期)), 0) from @t--结果 订单号 交货期 交货期所属周的周一 ---------- ---------- ---------- a1 2005-09-05 2005-09-05 a2 2005-09-08 2005-09-05 a3 2005-09-10 2005-09-05 a7 2005-09-13 2005-09-12 a9 2005-09-20 2005-09-19 a20 2005-09-27 2005-09-26 a200 2005-09-30 2005-09-26 a211 2005-10-30 2005-10-24
set datefirst 1 select convert(char(10),getdate()-datepart(weekday,getdate())+1,111) /* ---------- 2005/09/26(所影响的行数为 1 行) */ declare @t table(订单号 varchar(10), 交货期 datetime) insert into @t select 'a1','2005/9/5' union all select 'a2','2005/9/8' union all select 'a3','2005/9/10' union all select 'a7','2005/9/13' union all select 'a9','2005/9/20' union all select 'a20','2005/9/27' union all select 'a200','2005/9/30' union all select 'a211','2005/10/30'select 订单号,交货期, 交货期所属周的周一=convert(char(10),交货期-datepart(weekday,交货期)+1,111) from @t order by 交货期 /*订单号 交货期 交货期所属周的周一 ---------- ------------------------------------------------------ ---------- a1 2005-09-05 00:00:00.000 2005/09/05 a2 2005-09-08 00:00:00.000 2005/09/05 a3 2005-09-10 00:00:00.000 2005/09/05 a7 2005-09-13 00:00:00.000 2005/09/12 a9 2005-09-20 00:00:00.000 2005/09/19 a20 2005-09-27 00:00:00.000 2005/09/26 a200 2005-09-30 00:00:00.000 2005/09/26 a211 2005-10-30 00:00:00.000 2005/10/24(所影响的行数为 8 行) */
declare @t table(订单号 varchar(10), 交货期 datetime)
insert into @t select 'a1','2005/9/5'
union all select 'a2','2005/9/8'
union all select 'a3','2005/9/10'
union all select 'a7','2005/9/13'
union all select 'a9','2005/9/20'
union all select 'a20','2005/9/27'
union all select 'a200','2005/9/30'
union all select 'a211','2005/10/30'
--查询
select 订单号,交货期,交货期所属周的周一=DATEADD(wk, DATEDIFF(wk,0,dateadd(ms,-3,交货期)), 0)
from @t--结果
订单号 交货期 交货期所属周的周一
---------- ---------- ----------
a1 2005-09-05 2005-09-05
a2 2005-09-08 2005-09-05
a3 2005-09-10 2005-09-05
a7 2005-09-13 2005-09-12
a9 2005-09-20 2005-09-19
a20 2005-09-27 2005-09-26
a200 2005-09-30 2005-09-26
a211 2005-10-30 2005-10-24
select convert(char(10),getdate()-datepart(weekday,getdate())+1,111)
/*
----------
2005/09/26(所影响的行数为 1 行)
*/
declare @t table(订单号 varchar(10), 交货期 datetime)
insert into @t select 'a1','2005/9/5'
union all select 'a2','2005/9/8'
union all select 'a3','2005/9/10'
union all select 'a7','2005/9/13'
union all select 'a9','2005/9/20'
union all select 'a20','2005/9/27'
union all select 'a200','2005/9/30'
union all select 'a211','2005/10/30'select 订单号,交货期,
交货期所属周的周一=convert(char(10),交货期-datepart(weekday,交货期)+1,111)
from @t order by 交货期
/*订单号 交货期 交货期所属周的周一
---------- ------------------------------------------------------ ----------
a1 2005-09-05 00:00:00.000 2005/09/05
a2 2005-09-08 00:00:00.000 2005/09/05
a3 2005-09-10 00:00:00.000 2005/09/05
a7 2005-09-13 00:00:00.000 2005/09/12
a9 2005-09-20 00:00:00.000 2005/09/19
a20 2005-09-27 00:00:00.000 2005/09/26
a200 2005-09-30 00:00:00.000 2005/09/26
a211 2005-10-30 00:00:00.000 2005/10/24(所影响的行数为 8 行)
*/