日期:2014-05-18 浏览次数:20540 次
--设置星期一是一周的第一天 set datefirst 1 declare @t table ( date datetime ) declare @date datetime set @date = '2012-03-01' while ( @date < '2012-03-20' ) begin if ( datepart(w, @date) in ( 3, 5 ) ) insert into @t select @date set @date = dateadd(d, 1, @date) end select * from @t /* date ----------------------- 2012-03-02 00:00:00.000 2012-03-07 00:00:00.000 2012-03-09 00:00:00.000 2012-03-14 00:00:00.000 2012-03-16 00:00:00.000 */
------解决方案--------------------
--我刚才找的是周三和周五,修正一下 set datefirst 1 declare @t table ( date datetime ) declare @date datetime set @date = '2012-03-01' while ( @date < '2012-03-20' ) begin if ( datepart(w, @date) in ( 1, 3 ) ) insert into @t select @date set @date = dateadd(d, 1, @date) end select * from @t /* date ----------------------- 2012-03-05 00:00:00.000 2012-03-07 00:00:00.000 2012-03-12 00:00:00.000 2012-03-14 00:00:00.000 2012-03-19 00:00:00.000 */
------解决方案--------------------
set datefirst 1 declare @start datetime declare @end datetime set @start = '2012-03-01' set @end = '2012-03-20' ;with ach as ( select dateadd(dd,number,@start) date from master..spt_values where [type] = 'p' and number between 0 and datediff(dd,@start,@end) ) select * from ach where datepart(weekday,date) in (1,3)
------解决方案--------------------
select data from (select dateadd(day,number,'2012-3-1')data from master..spt_values where type='p' and number between 0 and datediff(dd,'2012-3-1','2012-3-20'))
t where datepart(weekday,t.data) in (1,3)