如何在SQL中获取给定月份的总周数和日期

Question

尊敬的专家，

根据我们公司的要求，我们需要任何月份的以下几周详细信息

例如如果月份是 4 月，年份是 2017 年，则查询结果应该是

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 重新格式化日期(

---

这可以通过日历表而不是函数轻松处理。

参考：

• 生成不带循环的集合或序列 2- 亚伦·伯特兰
• 在 SQL Server 2008 中创建日期表/维度 - David Stein
• 日历表 - 为什么你需要一个 - 大卫·斯坦
• 在 SQL Server 中创建日期维度或日历表 - Aaron Bertrand
• 用于确定 SQL Server 中的假日的 TSQL 函数 - Tim Cullen
• F_TABLE_DATE - 迈克尔·瓦伦丁·琼斯

Answer 2

这将做到这一点(它不依赖于设置的日期优先(：

-- 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