可以将文章内容翻译成中文,广告屏蔽插件可能会导致该功能失效(如失效，请关闭广告屏蔽插件后再试):
问题:
I need to get start and end date for each week in given month/year. (month and year are always given - like march 2017).
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?
回答1:
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)
);
and calling it like so:
set datefirst 1;
select * from dbo.udf_weeks_of_month('20170101');
returns:
+-------------+------+------------+------------+
| 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 |
+-------------+------+------------+------------+
and this call:
select * from dbo.udf_weeks_of_month('february 2017');
returns:
+-------------+------+------------+------------+
| 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 demo: http://rextester.com/VKPQU7936 (note: rextester reformats the dates)
回答2:
Something like this?
DECLARE @Dates TABLE (DateId INT IDENTITY, Dt Date);
DECLARE @STart Date = DATEADD(Year, DATEDIFF(Year, 0, GETDATE()),0)
SET NOCOUNT ON;
WHILE @STart <= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
BEGIN
INSERT INTO @Dates (Dt) VALUES (@STart)
SELECT @Start = DATEADD(DAY, 1, @STart)
END
Updated
SELECT
DateId
, Dt
, DATEADD(WEEK, DATEDIFF(DAY, 0, Dt)/7, 0) AS WeekBeginningMondayOf
, DATEADD(DAY, 6, DATEADD(WEEK, DATEDIFF(DAY, 0, Dt)/7, 0)) AS WeekEndingSundayOf
, DENSE_RANK() OVER(PARTITION BY MONTH(Dt) ORDER BY DATEADD(WEEK, DATEDIFF(DAY, 0, Dt)/7, 0)) AS WeekInMonth
FROM @Dates
回答3:
It is very smart in such situations to use a numbers / date / tally table. Such a table is very handsome in many situations!
In this answer I show an approach how to create and fill such a table.
Once you have created this table, the query is as simple as:
SELECT n.CalendarDate AS StartOfWeek
,DATEADD(DAY,6,n.CalendarDate) AS EndOfWeek
FROM dbo.RunningNumbers AS n
WHERE n.CalendarDate>={d'2017-01-01'} AND n.CalendarDate<{d'2017-02-01'}
AND n.CalendarWeekDay=1;
hint Such a table can easily be extended with information hardly to compute simply by adding some values into extra columns manually (e.g. holydays, free-of-work days...)
回答4:
So many variations on this theme. This one is a little shorter than other answers though.
declare @dt date = {fn current_date()};
declare @start_of_year date = datefromparts(year(@dt), 1, 1);
with digits(d) as (
select 0 union all select 1 union all select 2 union all select 3 union all
select 4 union all select 5 union all select 6 union all select 7
), wks as (
select
dateadd(week, d1.d * 8 + d0.d, dateadd(day, 1-datepart(weekday, @start_of_year), @start_of_year)) as week_start,
d1.d * 8 + d0.d as wk
from digits as d0 cross join digits as d1
)
select
case when year(week_start) < year(@dt)
then @start_of_year else week_start end as week_start,
case when year(dateadd(day, 6, week_start)) > year(@dt)
then datefromparts(year(@dt), 12, 31) else dateadd(day, 6, week_start) end as week_end
from wks
where wk between 0 and 53 and year(week_start) = year(@dt)
order by week_start;
回答5:
I think what you are looking for is here ->someone asked on stackoverflow is similar to what you are looking for it.
This is the final version, hope as per your expectations
I build on the above link hope this is what you are looking for ( Apologies new to stackoverflow, unformatted t-sql below)
DECLARE @sDate DATETIME,
@eDate DATETIME
SET @sDate = '2017-03-01'
SET @eDate = DATEADD(DAY,-1, CAST(Cast(DatePart(YEAR,@sdate) AS varchar(4))
+'-' + Cast((DatePart(MONTH,@sdate)+1) AS varchar(2))+ '-1' AS Date))
SET @sDate = CAST(Cast(DatePart(YEAR,@sdate) AS varchar(4)) +'-'
+ Cast(DatePart(MONTH,@sdate) AS varchar(2))+ '-1' AS Date)
DECLARE @startDayOfWeekOffSet int ;
SET @startDayOfWeekOffSet=
(SELECT DATEPART(DW,@sDate));
DECLARE @DaysToGetFirstSaturday int
SET @DaysToGetFirstSaturday= 7- @startDayOfWeekOffSet
DECLARE @firstEverStartOfWeekDay Date , @firstWeekEndDay Date
SET @firstEverStartOfWeekDay =@sDate;
IF @startDayOfWeekOffSet = 1 -- January
BEGIN
SET @firstEverStartOfWeekDay = @sDate
SET @DaysToGetFirstSaturday = 0
SET @firstWeekEndDay = @sDate END
ELSE BEGIN IF DATEPART(MONTH,@sDate) > DATEPART(MONTH,@firstEverStartOfWeekDay)
AND @startDayOfWeekOffSet = 7 --FirstSundayNewMonth
BEGIN
SET @firstEverStartOfWeekDay =@sDate
SET @firstWeekEndDay = @sDate END ELSE --NotASundayNewMonth
BEGIN
SET @firstWeekEndDay = DATEADD(DAY,@DaysToGetFirstSaturday,@firstEverStartOfWeekDay);
END END
SELECT @firstWeekEndDay AS firstWeekEndDay,
@firstEverStartOfWeekDay AS firstEverStartOfWeekDay,
@DaysToGetFirstSaturday AS DaysToGetFirstSaturday,
@startDayOfWeekOffSet AS startDayOfWeekOffSet ;
WITH cte AS
(SELECT 1 AS WeekNum,
@firstEverStartOfWeekDay StartDate,
@firstWeekEndDay EndDate
UNION ALL SELECT WeekNum + 1, --Case when @scenario = 'NewYear' Then
dateadd(DAY, 1, cte.EndDate) --Else dateadd(ww, 1, cte.StartDate) End as
StartDate,
CASE
WHEN StartDate = EndDate THEN DateAdd(DAY,-1,dateadd(DAY, 8, cte.EndDate))
ELSE dateadd(ww, 1, cte.EndDate)
END AS EndDate
FROM cte
WHERE dateadd(ww, 1, StartDate)<= @eDate )
SELECT WeekNum,
CASE
WHEN DatePart(YEAR, StartDate) < DATEPART(YEAR,@sDate)
THEN CAST(Cast(DatePart(YEAR,@sdate) AS varchar(4)) + '-1-1' AS Date)
WHEN DatePart(MONTH, StartDate) < DATEPART(MONTH,@sDate)
THEN CAST(Cast(DatePart(YEAR,@sdate) AS varchar(4)) + '-'
+ Cast(DatePart(MONTH,@sdate) AS varchar(2)) + '-1' AS Date)
ELSE StartDate
END AS StartDate,
CASE
WHEN DatePart(MONTH, EndDate) > DATEPART(MONTH,@eDate) THEN @eDate
ELSE EndDate
END AS EndDate
FROM cte
回答6:
select
DateValue
, WeekStart =convert(date,(
case when datepart(week,DateValue) =1
then convert(date, (datename(year,DateValue) +'0101'))
else dateadd(day,@@datefirst-datepart(weekday,DateValue)-(@@datefirst-1),DateValue)
end) )
, WeekEnd =convert(date,(
case when datepart(week,DateValue) =53
then convert(date, (datename(year,DateValue) +'1231'))
else dateadd(day,(@@datefirst)-datepart(weekday,DateValue)+(7-@@datefirst),DateValue)
end) )
from dates
rextester demo: http://rextester.com/KYKS44588
test setup:
set datefirst 1;
declare @fromdate date = '20161227', @thrudate date = '20201231';
;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, @thrudate)+1)
[DateValue]=convert(date,dateadd(day,row_number() over(order by (select 1))-1,@fromdate))
from n as deka cross join n as hecto cross join n as kilo cross join n as [tenK]
order by 1
)
, test as (
select
convert(varchar(10),DateValue,120) as Date
, WeekStart = convert(varchar(10),convert(date,(
case when datepart(week,DateValue) =1
then convert(date, (datename(year,DateValue) +'0101'))
else dateadd(day,@@datefirst-datepart(WeekDay,DateValue)-(@@datefirst-1),DateValue)
end) ),120)
, WeekEnd = convert(varchar(10),convert(date,(
case when datepart(week,DateValue) =53
then convert(date, (datename(year,DateValue) +'1231'))
else dateadd(day,(@@datefirst)-datepart(WeekDay,DateValue)+(7-@@datefirst),DateValue)
end) ),120)
, week= datepart(week,DateValue)
from dates
)
select *
, dayname= datename(weekday,date)
, weekstartdayname= datename(weekday,weekstart)
, weekenddayname= datename(weekday,weekend)
from test
where week > 51
or week < 3
order by 1
results:
+------------+------------+------------+------+-----------+------------------+----------------+
| Date | WeekStart | WeekEnd | week | dayname | weekstartdayname | weekenddayname |
+------------+------------+------------+------+-----------+------------------+----------------+
| 2016-12-27 | 2016-12-26 | 2016-12-31 | 53 | Tuesday | Monday | Saturday |
| 2016-12-28 | 2016-12-26 | 2016-12-31 | 53 | Wednesday | Monday | Saturday |
| 2016-12-29 | 2016-12-26 | 2016-12-31 | 53 | Thursday | Monday | Saturday |
| 2016-12-30 | 2016-12-26 | 2016-12-31 | 53 | Friday | Monday | Saturday |
| 2016-12-31 | 2016-12-26 | 2016-12-31 | 53 | Saturday | Monday | Saturday |
| 2017-01-01 | 2017-01-01 | 2017-01-01 | 1 | Sunday | Sunday | Sunday |
| 2017-01-02 | 2017-01-02 | 2017-01-08 | 2 | Monday | Monday | Sunday |
| 2017-01-03 | 2017-01-02 | 2017-01-08 | 2 | Tuesday | Monday | Sunday |
| 2017-01-04 | 2017-01-02 | 2017-01-08 | 2 | Wednesday | Monday | Sunday |
| 2017-01-05 | 2017-01-02 | 2017-01-08 | 2 | Thursday | Monday | Sunday |
| 2017-01-06 | 2017-01-02 | 2017-01-08 | 2 | Friday | Monday | Sunday |
| 2017-01-07 | 2017-01-02 | 2017-01-08 | 2 | Saturday | Monday | Sunday |
| 2017-01-08 | 2017-01-02 | 2017-01-08 | 2 | Sunday | Monday | Sunday |
| 2017-12-18 | 2017-12-18 | 2017-12-24 | 52 | Monday | Monday | Sunday |
| 2017-12-19 | 2017-12-18 | 2017-12-24 | 52 | Tuesday | Monday | Sunday |
| 2017-12-20 | 2017-12-18 | 2017-12-24 | 52 | Wednesday | Monday | Sunday |
| 2017-12-21 | 2017-12-18 | 2017-12-24 | 52 | Thursday | Monday | Sunday |
| 2017-12-22 | 2017-12-18 | 2017-12-24 | 52 | Friday | Monday | Sunday |
| 2017-12-23 | 2017-12-18 | 2017-12-24 | 52 | Saturday | Monday | Sunday |
| 2017-12-24 | 2017-12-18 | 2017-12-24 | 52 | Sunday | Monday | Sunday |
| 2017-12-25 | 2017-12-25 | 2017-12-31 | 53 | Monday | Monday | Sunday |
| 2017-12-26 | 2017-12-25 | 2017-12-31 | 53 | Tuesday | Monday | Sunday |
| 2017-12-27 | 2017-12-25 | 2017-12-31 | 53 | Wednesday | Monday | Sunday |
| 2017-12-28 | 2017-12-25 | 2017-12-31 | 53 | Thursday | Monday | Sunday |
| 2017-12-29 | 2017-12-25 | 2017-12-31 | 53 | Friday | Monday | Sunday |
| 2017-12-30 | 2017-12-25 | 2017-12-31 | 53 | Saturday | Monday | Sunday |
| 2017-12-31 | 2017-12-25 | 2017-12-31 | 53 | Sunday | Monday | Sunday |
| 2018-01-01 | 2018-01-01 | 2018-01-07 | 1 | Monday | Monday | Sunday |
| 2018-01-02 | 2018-01-01 | 2018-01-07 | 1 | Tuesday | Monday | Sunday |
| 2018-01-03 | 2018-01-01 | 2018-01-07 | 1 | Wednesday | Monday | Sunday |
| 2018-01-04 | 2018-01-01 | 2018-01-07 | 1 | Thursday | Monday | Sunday |
| 2018-01-05 | 2018-01-01 | 2018-01-07 | 1 | Friday | Monday | Sunday |
| 2018-01-06 | 2018-01-01 | 2018-01-07 | 1 | Saturday | Monday | Sunday |
| 2018-01-07 | 2018-01-01 | 2018-01-07 | 1 | Sunday | Monday | Sunday |
| 2018-01-08 | 2018-01-08 | 2018-01-14 | 2 | Monday | Monday | Sunday |
| 2018-01-09 | 2018-01-08 | 2018-01-14 | 2 | Tuesday | Monday | Sunday |
| 2018-01-10 | 2018-01-08 | 2018-01-14 | 2 | Wednesday | Monday | Sunday |
| 2018-01-11 | 2018-01-08 | 2018-01-14 | 2 | Thursday | Monday | Sunday |
| 2018-01-12 | 2018-01-08 | 2018-01-14 | 2 | Friday | Monday | Sunday |
| 2018-01-13 | 2018-01-08 | 2018-01-14 | 2 | Saturday | Monday | Sunday |
| 2018-01-14 | 2018-01-08 | 2018-01-14 | 2 | Sunday | Monday | Sunday |
| 2018-12-24 | 2018-12-24 | 2018-12-30 | 52 | Monday | Monday | Sunday |
| 2018-12-25 | 2018-12-24 | 2018-12-30 | 52 | Tuesday | Monday | Sunday |
| 2018-12-26 | 2018-12-24 | 2018-12-30 | 52 | Wednesday | Monday | Sunday |
| 2018-12-27 | 2018-12-24 | 2018-12-30 | 52 | Thursday | Monday | Sunday |
| 2018-12-28 | 2018-12-24 | 2018-12-30 | 52 | Friday | Monday | Sunday |
| 2018-12-29 | 2018-12-24 | 2018-12-30 | 52 | Saturday | Monday | Sunday |
| 2018-12-30 | 2018-12-24 | 2018-12-30 | 52 | Sunday | Monday | Sunday |
| 2018-12-31 | 2018-12-31 | 2018-12-31 | 53 | Monday | Monday | Monday |
| 2019-01-01 | 2019-01-01 | 2019-01-06 | 1 | Tuesday | Tuesday | Sunday |
| 2019-01-02 | 2019-01-01 | 2019-01-06 | 1 | Wednesday | Tuesday | Sunday |
| 2019-01-03 | 2019-01-01 | 2019-01-06 | 1 | Thursday | Tuesday | Sunday |
| 2019-01-04 | 2019-01-01 | 2019-01-06 | 1 | Friday | Tuesday | Sunday |
| 2019-01-05 | 2019-01-01 | 2019-01-06 | 1 | Saturday | Tuesday | Sunday |
| 2019-01-06 | 2019-01-01 | 2019-01-06 | 1 | Sunday | Tuesday | Sunday |
| 2019-01-07 | 2019-01-07 | 2019-01-13 | 2 | Monday | Monday | Sunday |
| 2019-01-08 | 2019-01-07 | 2019-01-13 | 2 | Tuesday | Monday | Sunday |
| 2019-01-09 | 2019-01-07 | 2019-01-13 | 2 | Wednesday | Monday | Sunday |
| 2019-01-10 | 2019-01-07 | 2019-01-13 | 2 | Thursday | Monday | Sunday |
| 2019-01-11 | 2019-01-07 | 2019-01-13 | 2 | Friday | Monday | Sunday |
| 2019-01-12 | 2019-01-07 | 2019-01-13 | 2 | Saturday | Monday | Sunday |
| 2019-01-13 | 2019-01-07 | 2019-01-13 | 2 | Sunday | Monday | Sunday |
| 2019-12-23 | 2019-12-23 | 2019-12-29 | 52 | Monday | Monday | Sunday |
| 2019-12-24 | 2019-12-23 | 2019-12-29 | 52 | Tuesday | Monday | Sunday |
| 2019-12-25 | 2019-12-23 | 2019-12-29 | 52 | Wednesday | Monday | Sunday |
| 2019-12-26 | 2019-12-23 | 2019-12-29 | 52 | Thursday | Monday | Sunday |
| 2019-12-27 | 2019-12-23 | 2019-12-29 | 52 | Friday | Monday | Sunday |
| 2019-12-28 | 2019-12-23 | 2019-12-29 | 52 | Saturday | Monday | Sunday |
| 2019-12-29 | 2019-12-23 | 2019-12-29 | 52 | Sunday | Monday | Sunday |
| 2019-12-30 | 2019-12-30 | 2019-12-31 | 53 | Monday | Monday | Tuesday |
| 2019-12-31 | 2019-12-30 | 2019-12-31 | 53 | Tuesday | Monday | Tuesday |
| 2020-01-01 | 2020-01-01 | 2020-01-05 | 1 | Wednesday | Wednesday | Sunday |
| 2020-01-02 | 2020-01-01 | 2020-01-05 | 1 | Thursday | Wednesday | Sunday |
| 2020-01-03 | 2020-01-01 | 2020-01-05 | 1 | Friday | Wednesday | Sunday |
| 2020-01-04 | 2020-01-01 | 2020-01-05 | 1 | Saturday | Wednesday | Sunday |
| 2020-01-05 | 2020-01-01 | 2020-01-05 | 1 | Sunday | Wednesday | Sunday |
| 2020-01-06 | 2020-01-06 | 2020-01-12 | 2 | Monday | Monday | Sunday |
| 2020-01-07 | 2020-01-06 | 2020-01-12 | 2 | Tuesday | Monday | Sunday |
| 2020-01-08 | 2020-01-06 | 2020-01-12 | 2 | Wednesday | Monday | Sunday |
| 2020-01-09 | 2020-01-06 | 2020-01-12 | 2 | Thursday | Monday | Sunday |
| 2020-01-10 | 2020-01-06 | 2020-01-12 | 2 | Friday | Monday | Sunday |
| 2020-01-11 | 2020-01-06 | 2020-01-12 | 2 | Saturday | Monday | Sunday |
| 2020-01-12 | 2020-01-06 | 2020-01-12 | 2 | Sunday | Monday | Sunday |
| 2020-12-21 | 2020-12-21 | 2020-12-27 | 52 | Monday | Monday | Sunday |
| 2020-12-22 | 2020-12-21 | 2020-12-27 | 52 | Tuesday | Monday | Sunday |
| 2020-12-23 | 2020-12-21 | 2020-12-27 | 52 | Wednesday | Monday | Sunday |
| 2020-12-24 | 2020-12-21 | 2020-12-27 | 52 | Thursday | Monday | Sunday |
| 2020-12-25 | 2020-12-21 | 2020-12-27 | 52 | Friday | Monday | Sunday |
| 2020-12-26 | 2020-12-21 | 2020-12-27 | 52 | Saturday | Monday | Sunday |
| 2020-12-27 | 2020-12-21 | 2020-12-27 | 52 | Sunday | Monday | Sunday |
| 2020-12-28 | 2020-12-28 | 2020-12-31 | 53 | Monday | Monday | Thursday |
| 2020-12-29 | 2020-12-28 | 2020-12-31 | 53 | Tuesday | Monday | Thursday |
| 2020-12-30 | 2020-12-28 | 2020-12-31 | 53 | Wednesday | Monday | Thursday |
| 2020-12-31 | 2020-12-28 | 2020-12-31 | 53 | Thursday | Monday | Thursday |
+------------+------------+------------+------+-----------+------------------+----------------+
Calendar and Numbers table references:
- Generate a set or sequence without loops - 1 - Aaron Bertrand
- Generate a set or sequence without loops - 3 - Aaron Bertrand
- The "Numbers" or "Tally" Table: What it is and how it replaces a loop - Jeff Moden
- 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
{var%20f='http://v.t.sina.com.cn/share/share.php?appkey=1515056452',u=z||d.location,p=['&url=',e(u),'&title=',e(t||d.title),'&source=',e(r),'&sourceUrl=',e(l),'&content=',c||'gb2312','&pic=',e(p||'')].join('');function%20a(){if(!window.open([f,p].join(''),'mb',['toolbar=0,status=0,resizable=1,width=440,height=430,left=',(s.width-440)/2,',top=',(s.height-430)/2].join('')))u.href=[f,p].join('');};if(/Firefox/.test(navigator.userAgent))setTimeout(a,0);else%20a();})(screen,document,encodeURIComponent,'','','https://i.stack.imgur.com/AWfKQ.png', '推荐 三岁会撩人 的文章《Get start and end date for each week in month》','https://www.manongdao.com/article-1210265.html','页面编码gb2312|utf-8默认gb2312'));){kind=link}