获取每月每周的开始和结束日期 [英] 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屋!
查看全文