返回日期所在月的周的起止日期SQL

Question

SELECT CONVERT(VARCHAR(10),MIN(DAT),120) AS DATESTART, CONVERT(VARCHAR(10),MAX(DAT),120) AS DATEEND
FROM (
SELECT DATEADD(DAY, ID-1, CONVERT(VARCHAR(8),GETDATE(),120)+'01') AS DAT
FROM (SELECT ID=(SELECT COUNT(*) FROM SYSOBJECTS WHERE ID<=O.ID) FROM SYSOBJECTS O) T
WHERE DAY(DATEADD(DAY, -1, DATEADD(MONTH, 1, CONVERT(VARCHAR(8),GETDATE(),120)+'01')))>=ID
) T
GROUP BY DATEPART(WK,DAT)
/*
DATESTART  DATEEND
---------- ----------
2009-04-01 2009-04-04
2009-04-05 2009-04-11
2009-04-12 2009-04-18
2009-04-19 2009-04-25
2009-04-26 2009-04-30
*/

Answer 1

select w1b=dt,w1e=dt+6,w2b=dt+7,w2e=dt+13,w3b=dt+14,w3e=dt+20,w4b=dt+21,w4e=dt+27,w5b=dt+28,w5e=dt+34
from(select dt=dateadd(wk,datediff(wk,0,dateadd(m,datediff(m,0,getdate()),0)),0)) t
/*
w1b                     w1e                     w2b                     w2e                     w3b                     w3e                     w4b                     w4e                     w5b                     w5e
----------------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- -----------------------
2009-03-30 00:00:00.000 2009-04-05 00:00:00.000 2009-04-06 00:00:00.000 2009-04-12 00:00:00.000 2009-04-13 00:00:00.000 2009-04-19 00:00:00.000 2009-04-20 00:00:00.000 2009-04-26 00:00:00.000 2009-04-27 00:00:00.000 2009-05-03 00:00:00.000(1 行受影响)
*/