sql 如何取到每个月第三周的星期一

[cxz_azhong4812]

sql 如何取到每个月第三周的星期一，在线求救！ 先谢各位了！

[叶子]

对SQL2000不熟悉，等高人出手！

[onepixels]

这是个难题？

[otnto]

1楼的求的是每月第三个星期一，而非每月第三周星期一

[gw6328]

declare @d datetime;
set @d=DATEADD (wk,datediff(wk,0,dateadd(wk,2,dateadd(DAY,1-datepart(day,getdate()),getdate()))),0)
select @d

[gw6328]

[Quote=引用 10 楼 jinfengyiye 的回复:]
SQL code


declare @d datetime;
set @d=DATEADD (wk,datediff(wk,0,dateadd(wk,2,dateadd(DAY,1-datepart(day,getdate()),getdate()))),0)
select @d

/*
-----------------------
2011-01-10 00:00:00.000

(1 行受影响)
*/
[/Quote]
...

[cxz_azhong4812]

[Quote=引用 9 楼 otnto 的回复:]
1楼的求的是每月第三个星期一，而非每月第三周星期一
[/Quote]

每月第三周星期一

[otnto]

一楼的修正下，

select * from 
(select dense_rank() over(partition by datepart(month,dateadd(dd,number,'2011-01-01')) order by datepart(wk,dateadd(dd,number,'2011-01-01'))) as num ,
datepart(month,dateadd(dd,number,'2011-01-01')) as mon,dateadd(dd,number,'2011-01-01') as date
from master..spt_values
where type='p' and number < datediff(dd,'2011-01-01','2012-01-01') 
)a
where a.num=3 and datepart(dw,date)=2

num                  mon         date
-------------------- ----------- -----------------------
3                    1           2011-01-10 00:00:00.000
3                    2           2011-02-14 00:00:00.000
3                    3           2011-03-14 00:00:00.000
3                    4           2011-04-11 00:00:00.000
3                    5           2011-05-16 00:00:00.000
3                    6           2011-06-13 00:00:00.000
3                    7           2011-07-11 00:00:00.000
3                    8           2011-08-15 00:00:00.000
3                    9           2011-09-12 00:00:00.000
3                    10          2011-10-10 00:00:00.000
3                    11          2011-11-14 00:00:00.000
3                    12          2011-12-12 00:00:00.000

[Linares]

declare @ datetime
select @ = getdate(), @ = @ - day(@), @ = @ + 15 - (@@datefirst-1+datepart(weekday,@))%7
select @

[daishaodong]

基本思路，我看得先确定每个月的第一个星期一是几号，然后+14，得到该月的第3个星期一；

[Linares]

[Quote=引用 15 楼 daishaodong 的回复:]

基本思路，我看得先确定每个月的第一个星期一是几号，然后+14，得到该月的第3个星期一；
[/Quote]

是每月第三周的星期一，并非每月第三个星期一。

[快溜]

select * from 
(select row_number() over(partition by datepart(month,dateadd(dd,number,'2011-01-01')) order by datepart(month,dateadd(dd,number,'2011-01-01'))) as num ,
datepart(month,dateadd(dd,number,'2011-01-01')) as mon,dateadd(dd,number,'2011-01-01') as date
from master..spt_values
where type='p' and number < datediff(dd,'2011-01-01','2012-01-01') 
    and DatePart(weekday,dateadd(dd,number,'2011-01-01'))=2) a where a.num=3

[cxz_azhong4812]

有错误啊，执行不了！

[cxz_azhong4812]

这段语句有问题啊！[Quote=引用 1 楼 ssp2009 的回复:]
SQL code
select * from
(select row_number() over(partition by datepart(month,dateadd(dd,number,'2011-01-01')) order by datepart(month,dateadd(dd,number,'2011-01-01'))) as num ,
datepart(month,date……
[/Quote]

[快溜]

num                  mon         date
-------------------- ----------- -----------------------
3                    1           2011-01-17 00:00:00.000
3                    2           2011-02-21 00:00:00.000
3                    3           2011-03-21 00:00:00.000
3                    4           2011-04-18 00:00:00.000
3                    5           2011-05-16 00:00:00.000
3                    6           2011-06-20 00:00:00.000
3                    7           2011-07-18 00:00:00.000
3                    8           2011-08-15 00:00:00.000
3                    9           2011-09-19 00:00:00.000
3                    10          2011-10-17 00:00:00.000
3                    11          2011-11-21 00:00:00.000
3                    12          2011-12-19 00:00:00.000

你数据库什么版本？

[cxz_azhong4812]

[Quote=引用 4 楼 ssp2009 的回复:]
SQL code
num mon date
-------------------- ----------- -----------------------
3 1 2011-01-17 00:00:00.000
3 2 20……
[/Quote]
2000的！

[linbiao523]

declare @Year varchar(4)
set @Year='2011'
select Number [Month],dateadd(dd,-datepart(weekday,Date),dateadd(dd,16,Date)) as Date
from 
(
	select Number,convert(datetime,@Year+'-'+convert(varchar,Number)+'-01') Date
	from master..spt_values
	where type='P' and number between 1 and 12
) a

思路:每个月1号加16天,在减去每个月第一天星期几数.因为如果一号是星期,则刚好加16天,否则减去多加的天数

[dang520yutou]

DECLARE @S0 INT;
SET @S0=1
IF ( SELECT OBJECT_ID( 'TEMPDB..#TMP_RQ' ) ) > 0 DROP TABLE #TMP_RQ;

CREATE TABLE #TMP_RQ
(
OBJ datetime
）
WHILE(@S0<13)
BEGIN
INSERT INTO #TMP_RQ(OBJ)
SELECT DATEADD(DAY,DATEPART(WEEKDAY,dateadd(MONTH,@S0-1,dateadd(DAY,1-datepart(day,getdate()),getdate()))),dateadd(wEEk,2,dateadd(MONTH,@S0-1,dateadd(DAY,1-datepart(day,getdate()),getdate()))))
SET @S0=@S0+1
END

SELECT CONVERT(VARCHAR(10),OBJ,120) 本年每月第三周星期一 FROM #TMP_RQ

[华夏小卒]

set datefirst 1

select * from
(
	select DATEADD(DAY,number,'2010-01-01') as rq,
		dense_rank()over(partition by MONTH(DATEADD(DAY,number,'2010-01-01')) order by DATEPART(WEEK,DATEADD(DAY,number,'2010-01-01'))) as zz
	from master..spt_values
	where number >=0 and type='p' and DATEADD(DAY,number,'2010-01-01')<='2011-01-31'
)t
where zz=3 and DATEPART(weekday,rq)=1
 
 
 
 rq                      zz
----------------------- --------------------
2010-01-11 00:00:00.000 3
2011-01-10 00:00:00.000 3
2010-02-15 00:00:00.000 3
2010-03-15 00:00:00.000 3
2010-04-12 00:00:00.000 3
2010-05-10 00:00:00.000 3
2010-06-14 00:00:00.000 3
2010-07-12 00:00:00.000 3
2010-08-09 00:00:00.000 3
2010-09-13 00:00:00.000 3
2010-10-11 00:00:00.000 3
2010-11-15 00:00:00.000 3
2010-12-13 00:00:00.000 3

(13 row(s) affected)

[Dear SQL(燊)]

declare @date datetime
set @date =DATEADD(D,-DAY(getdate())+1,getdate())
select @date='2010/1/1'
select dateadd(D,7-Datepart(w,@date)-1+10,@date)

[binyifan]

[Quote=引用 17 楼 linbiao523 的回复:]
SQL code

declare @Year varchar(4)
set @Year='2011'
select Number [Month],dateadd(dd,-datepart(weekday,Date),dateadd(dd,16,Date)) as Date
from
(
select Number,convert(datetime,@Year+'-'+co……
[/Quote]



select dt= dt- DATEPART(WEEKDAY, dt)+15
from (
select dt = dateadd( mm ,number-1,CONVERT(datetime,'2011-01-01'))
from master..spt_values
where type='P' and number between 1 and 12
) a