获取每月每周的开始和结束日期 [英] Get start and end date for each week in month

问题描述

我需要获取给定月份/年份中每周的开始和结束日期.(总是给出月份和年份——比如 2017 年 3 月).

I need to get start and end date for each week in given month/year. (month and year are always given - like march 2017).

示例，2017 年 1 月:

Example, january 2017:

1 week : '2017-01-01' - '2017-01-01'
2 week:  '2017-01-02' - '2017-01-08'
3 week:  '2017-01-09' - '2017-01-15'
4 week:  '2017-01-16' - '2017-01-22'
5 week:  '2017-01-23' - '2017-01-29'
6 week:  '2017-01-30' - '2017-01-31'

我已经知道如何获得给定月/年的周数:

I already know how to get number of weeks for given month / year:

select *,
    DATEDIFF(WEEK, DATEADD(day,-1,StartAt), DATEADD(day,-1,EndAt)) +1
    as NumWeeks

但是如何获取给定月/年每周的开始/结束日期?

But how to get start / end date for each week for the given month/year?

推荐答案

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

If you just want a function to return the weeks of a month for one month then this would do what you want:

create function dbo.udf_weeks_of_month (@fromdate date) 
returns table 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))
  , Week        = datepart(week,DateValue)
  , WeekStart   = min(DateValue)
  , WeekEnd     = max(DateValue)
from dates
group by datepart(week,DateValue)
);

并像这样称呼它:

set datefirst 1;
select * from dbo.udf_weeks_of_month('20170101');

返回:

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

还有这个电话:

select * from dbo.udf_weeks_of_month('february 2017');

返回:

+-------------+------+------------+------------+
| WeekOfMonth | Week | WeekStart  |  WeekEnd   |
+-------------+------+------------+------------+
|           1 |    6 | 2017-02-01 | 2017-02-05 |
|           2 |    7 | 2017-02-06 | 2017-02-12 |
|           3 |    8 | 2017-02-13 | 2017-02-19 |
|           4 |    9 | 2017-02-20 | 2017-02-26 |
|           5 |   10 | 2017-02-27 | 2017-02-28 |
+-------------+------+------------+------------+

rextester 演示:http://rextester.com/VKPQU7936(注意:rextester 重新格式化日期)

rextester demo: http://rextester.com/VKPQU7936 (note: rextester reformats the dates)

这篇关于获取每月每周的开始和结束日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！