declare @dt varchar(7) set @dt='1037' select datediff(d,dateadd(d,number,'20'+LEFT(@dt,2)+'-01-01'),GETDATE()) daycount from master..spt_values where type='p' and datepart(wk,dateadd(d,number,'20'+LEFT(@dt,2)+'-01-01'))=convert(int,right(@dt,2)) and datepart(dw,dateadd(d,number,'20'+LEFT(@dt,2)+'-01-01'))=1 and number<365 /* daycount ----------- 382(1 行受影响)*/
set datefirst 1 declare @date varvhar(10) set @date='1037' select datediff(dd,t,getdate())+datepart(dw,t) from (select dateadd(wk,cast(right(@date,2) as int))-1,'20'+left(@date,2)+'-01-01')t
set @dt='1037'
select datediff(d,dateadd(d,number,'20'+LEFT(@dt,2)+'-01-01'),GETDATE()) daycount from master..spt_values
where type='p' and datepart(wk,dateadd(d,number,'20'+LEFT(@dt,2)+'-01-01'))=convert(int,right(@dt,2))
and datepart(dw,dateadd(d,number,'20'+LEFT(@dt,2)+'-01-01'))=1 and number<365
/*
daycount
-----------
382(1 行受影响)*/
declare @dt varchar(7)
set @dt='1037'
select DATEDIFF(d,dt,getdate())+DATEPART(dw,dt)-1 from(
select DATEADD(wk,convert(int,right(@dt,2))-1,'20'+LEFT(@dt,2)+'-01-01')dt
)t
/*
-----------
382(1 行受影响)*/
declare @date varvhar(10)
set @date='1037'
select
datediff(dd,t,getdate())+datepart(dw,t)
from
(select dateadd(wk,cast(right(@date,2) as int))-1,'20'+left(@date,2)+'-01-01')t