不好意思,结果和楼主说的不一样: select distinct datepart(wk,dateadd(d,number,'2011-07-11')) from master..spt_values where type='p' and dateadd(d,number,'2011-07-11')<='2011-07-22' /* ----------- 29 30(2 行受影响)*/
declare @date1 datetime,@date2 datetime set @date1='2011-07-11' set @date2='2011-07-22'select distinct datediff(wk,DATENAME (yy,@date1)+'-01-01',dateadd(dd,number,@date1)) as weeks from master..spt_values WHERE type='P' and dateadd(dd,number,@date1)<=@date2/* weeks ----------- 28 29(所影响的行数为 2 行)*/
declare @date1 datetime,@date2 datetime set @date1='2011-07-11' set @date2='2011-07-22'select distinct datediff(wk,DATENAME (yy,@date1)+'-01-01',dateadd(dd,number,@date1)) as weeks from master..spt_values WHERE type='P' and dateadd(dd,number,@date1)<=@date2
select distinct datepart(wk,dateadd(d,number,'2011-07-11')) from master..spt_values where type='p' and dateadd(d,number,'2011-07-11')<='2011-07-22'
/*
-----------
29
30(2 行受影响)*/
select datepart(wk,'2011-07-11')
/*
-----------
29(1 行受影响)*/
set @date1='2011-07-11'
set @date2='2011-07-22'select distinct datediff(wk,DATENAME (yy,@date1)+'-01-01',dateadd(dd,number,@date1)) as weeks
from master..spt_values
WHERE type='P'
and dateadd(dd,number,@date1)<=@date2/*
weeks
-----------
28
29(所影响的行数为 2 行)*/
set @date1='2011-07-11'
set @date2='2011-07-22'select distinct datediff(wk,DATENAME (yy,@date1)+'-01-01',dateadd(dd,number,@date1)) as weeks
from master..spt_values
WHERE type='P'
and dateadd(dd,number,@date1)<=@date2