set datefirst 1 select * from tb where datepart(dw,datecol) not in (6,7)
--//一年中所有星期二 select dateadd(day,x,col),'星期二' from ( select cast('2009-1-1' as datetime) as col )a cross join ( SELECT top 365 b8.i+b7.i + b6.i + b5.i + b4.i +b3.i +b2.i + b1.i + b0.i x FROM(SELECT 0 i UNION ALL SELECT 1) b0 CROSS JOIN(SELECT 0 i UNION ALL SELECT 2) b1 CROSS JOIN(SELECT 0 i UNION ALL SELECT 4) b2 CROSS JOIN(SELECT 0 i UNION ALL SELECT 8) b3 CROSS JOIN(SELECT 0 i UNION ALL SELECT 16) b4 CROSS JOIN(SELECT 0 i UNION ALL SELECT 32) b5 CROSS JOIN(SELECT 0 i UNION ALL SELECT 64) b6 CROSS JOIN(SELECT 0 i UNION ALL SELECT 128) b7 CROSS JOIN(SELECT 0 i UNION ALL SELECT 256) b8 order by 1 )b where datepart(dw,dateadd(day,x,col))=3
select *
from tb
where datepart(dw,datecol) not in (6,7)
select dateadd(day,x,col),'星期二' from
(
select cast('2009-1-1' as datetime) as col
)a cross join
(
SELECT top 365 b8.i+b7.i + b6.i + b5.i + b4.i +b3.i +b2.i + b1.i + b0.i x
FROM(SELECT 0 i UNION ALL SELECT 1) b0
CROSS JOIN(SELECT 0 i UNION ALL SELECT 2) b1
CROSS JOIN(SELECT 0 i UNION ALL SELECT 4) b2
CROSS JOIN(SELECT 0 i UNION ALL SELECT 8) b3
CROSS JOIN(SELECT 0 i UNION ALL SELECT 16) b4
CROSS JOIN(SELECT 0 i UNION ALL SELECT 32) b5
CROSS JOIN(SELECT 0 i UNION ALL SELECT 64) b6
CROSS JOIN(SELECT 0 i UNION ALL SELECT 128) b7
CROSS JOIN(SELECT 0 i UNION ALL SELECT 256) b8
order by 1
)b
where datepart(dw,dateadd(day,x,col))=3