sql算某段时间有几个星期一,取本月所有的周三, 取上个月所有的周三 etc

sql算某段时间有几个星期一

DECLARE @t TABLE(dt DATETIME)
DECLARE @dtStart DATETIME
DECLARE @dtEnd DATETIME

SET @dtStart='2014-10-01'
SET @dtEnd='2014-10-31'

WHILE @dtStart <= @dtEnd
BEGIN
     IF DATEPART(WEEKDAY,@dtStart)=2 INSERT INTO @t SELECT @dtStart
     SET @dtStart=DATEADD(DAY,1,@dtStart)
END

SELECT * FROM @t

 /*

 DATEPART(WEEKDAY,@dtStart)=1  表示周日，星期天
 DATEPART(WEEKDAY,@dtStart)=2  表示周一

 SET @dtStart='2008-06-01'
SET @dtEnd='2008-07-01'

 2008-06-01 00:00:00.000
 2008-06-08 00:00:00.000
 2008-06-15 00:00:00.000
 2008-06-22 00:00:00.000
 2008-06-29 00:00:00.000
 */

 /*
 2014-10-06 00:00:00.000
2014-10-13 00:00:00.000
2014-10-20 00:00:00.000
2014-10-27 00:00:00.000
 */

 

-- 取本月所有的周三
DECLARE @t TABLE(dt DATETIME)
DECLARE @dtStart DATETIME
DECLARE @dtEnd DATETIME

SET @dtStart=CONVERT(datetime,CONVERT(char(8),GETDATE(),120)+'1') -- 取本月第一天 2014-10-01 00:00:00.000  
SET @dtEnd=dateadd(d,-day(getdate()),dateadd(m,1,getdate())) -- 取本月第后一天 2014-10-31 14:10:34.890  

WHILE @dtStart <= @dtEnd
BEGIN
     IF DATEPART(WEEKDAY,@dtStart)=4 INSERT INTO @t SELECT @dtStart
     SET @dtStart=DATEADD(DAY,1,@dtStart)
END

SELECT * FROM @t

 /*

 DATEPART(WEEKDAY,@dtStart)=1  表示周日，星期天
 DATEPART(WEEKDAY,@dtStart)=4  表示周三
 
2014-10-01 00:00:00.000
2014-10-08 00:00:00.000
2014-10-15 00:00:00.000
2014-10-22 00:00:00.000
2014-10-29 00:00:00.000
 */

 

-- 取上个月所有的周三
DECLARE @t TABLE(dt DATETIME)
DECLARE @dtStart DATETIME
DECLARE @dtEnd DATETIME

SET @dtStart=DATEADD(mm,DATEDIFF(mm,0,dateadd(month,-1,getdate())),0)--上月第一天 
SET @dtEnd=dateadd(ms,-3,DATEADD(mm,DATEDIFF(mm,0,getdate()),0))--上月最后一天

WHILE @dtStart <= @dtEnd
BEGIN
     IF DATEPART(WEEKDAY,@dtStart)=4 INSERT INTO @t SELECT @dtStart
     SET @dtStart=DATEADD(DAY,1,@dtStart)
END

SELECT * FROM @t

 /*

 DATEPART(WEEKDAY,@dtStart)=1  表示周日，星期天
 DATEPART(WEEKDAY,@dtStart)=4  表示周三
 
2014-09-03 00:00:00.000
2014-09-10 00:00:00.000
2014-09-17 00:00:00.000
2014-09-24 00:00:00.000

 */

 

	--计算 某个月所有的星期五

	-- 方法1
	--@date的格式应该为 200806
    declare @datestr varchar(10)='201410'  --这里可以自己改  
    declare @s datetime,@weekdaycount int
    declare @tbl table(date datetime)
    set @s=@datestr+'01'
    while @s<dateadd(month,1,@datestr+'01')
    begin
        if datepart(weekday,@s)=6 --这里可以自己改  6表示为星期五
        insert into @tbl select @s
        set @s=dateadd(day,1,@s)
    end
    --select @weekdaycount=count(*) from @tbl  
    --select @weekdaycount
	select * from @tbl  
/*
2014-10-03 00:00:00.000
2014-10-10 00:00:00.000
2014-10-17 00:00:00.000
2014-10-24 00:00:00.000
2014-10-31 00:00:00.000

(5 行受影响)
*/

 

-- 计算某个月所有的星期五
DECLARE @t TABLE(dt DATETIME)  
DECLARE @dtStart DATETIME  
DECLARE @dtEnd DATETIME  
  
SET @dtStart='2014-10-01'  
SET @dtEnd='2014-10-31'  
  
WHILE @dtStart <= @dtEnd  
BEGIN  
     IF DATEPART(WEEKDAY,@dtStart)=6 INSERT INTO @t SELECT @dtStart  
     SET @dtStart=DATEADD(DAY,1,@dtStart)  
END  
  
SELECT * FROM @t  
/*
-----------------------
2014-10-03 00:00:00.000
2014-10-10 00:00:00.000
2014-10-17 00:00:00.000
2014-10-24 00:00:00.000
2014-10-31 00:00:00.000

(5 行受影响)
*/