如何总周日期为给定月份获得SQL

Question

尊敬的专家，

根据我们公司的要求，我们需要几个星期的详细信息任何一个月

例如 如果一个月是四月和年是比查询结果应该是

weeknumber  startdate  enddate 
    1   01-04-2017 01-04-2017  
    2   02-04-2017 08-04-2017 
    3   09-04-2017 15-04-2017 
    4   16-04-2017 22-04-2017 
    5   23-04-2017 29-04-2017 
    6   30-04-2017 30-04-2017 

随着我只能在一个月算周搜索，但我需要开始日期和结束日期应该是开始从周日到周六。

非常感谢您提前。

Answer 1

如果你只是想返回一个月一个月的周函数，那么这会做你想要什么：

create function dbo.udf_weeks_of_month (@fromdate date) 
returns table with schemabinding as return (
with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n)) 
, dates as (
    select top (datediff(day, @fromdate, dateadd(month, datediff(month, 0, @fromdate)+1, 0))) 
    [DateValue]=convert(date,dateadd(day,row_number() over(order by (select 1))-1,@fromdate)) 
    from n as deka cross join n as hecto 
) 
select 
    WeekOfMonth = row_number() over (order by datepart(week,DateValue)) 
    , WeekStart = min(DateValue) 
    , WeekEnd  = max(DateValue) 
from dates 
group by datepart(week,DateValue) 
); 

，并调用它像这样：

set datefirst 7; 
select 
    WeekOfMonth 
    , WeekStart 
    , WeekEnd 
from dbo.udf_weeks_of_month('20170401'); 

回报：

+-------------+------------+------------+ 
| WeekOfMonth | WeekStart | WeekEnd | 
+-------------+------------+------------+ 
|   1 | 2017-04-01 | 2017-04-01 | 
|   2 | 2017-04-02 | 2017-04-08 | 
|   3 | 2017-04-09 | 2017-04-15 | 
|   4 | 2017-04-16 | 2017-04-22 | 
|   5 | 2017-04-23 | 2017-04-29 | 
|   6 | 2017-04-30 | 2017-04-30 | 
+-------------+------------+------------+ 

rextester演示：http://rextester.com/FSA98744（注：rextester重新格式化日期）

---

这可以用日历表而非函数轻松处理。

参考：

• Generate a set or sequence without loops 2- Aaron Bertrand
• Creating a Date Table/Dimension in SQL Server 2008 - David Stein
• Calendar Tables - Why You Need One - David Stein
• Creating a date dimension or calendar table in SQL Server - Aaron Bertrand
• TSQL Function to Determine Holidays in SQL Server - Tim Cullen
• F_TABLE_DATE - Michael Valentine Jones

Answer 2

这将做到这一点（它不依赖于一套DATEFIRST）：

-- can be any date within the month 
DECLARE @d date = '2017-04-01' 
SET @d = dateadd(mm,datediff(mm,0,@d),0) 
;WITH CTE(dat) as 
(
    SELECT top(datediff(d, @d, dateadd(mm,1, @d))) 
    cast(dateadd(d, row_number() over(order by (SELECT null))-1, @d) as date) 
    FROM 
    (values(0),(0),(0),(0),(0),(0)) s(n), 
    (values(0),(0),(0),(0),(0),(0)) t(n) 
) 
SELECT 
datediff(week, @d,dat)+1 wk, 
min(dat) startdate, 
max(dat) enddate 
FROM CTE 
GROUP BY datediff(week, @d,dat) 

结果：

wk startdate enddate 
1 2017-04-01 2017-04-01 
2 2017-04-02 2017-04-08 
3 2017-04-09 2017-04-15 
4 2017-04-16 2017-04-22 
5 2017-04-23 2017-04-29 
6 2017-04-30 2017-04-30