Get dates from a week number in T-SQL
Solution 1
Quassnoi's answer works, but kind of leaves you on the hook for cleaning up the dates if they are dates in the middle of the day (his start of week leaves you one day earlier than you need to be if you use a time in the middle of the day -- you can test using GETDATE()).
I've used something like this in the past:
SELECT
CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, DATECOL), DATECOL)), 101),
CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, DATECOL) - 6, DATECOL)), 101)
A side benefit of this is that by using @@DATEFIRST you can handle nonstandard week starting days (the default is Sunday, but with SET @@DATEFIRST you can change this).
It seems crazy that simple date manipulation in SQL Server has to be this arcane, but there you go...
Solution 2
You can set @WeekNum and @YearNum to whatever you want - in this example they are derived from the @datecol variable, which is set to GETDATE() for purposes of illustration. Once you have those values- you can calculate the date range for a week by using the following:
DECLARE @datecol datetime = GETDATE();
DECLARE @WeekNum INT
, @YearNum char(4);
SELECT @WeekNum = DATEPART(WK, @datecol)
, @YearNum = CAST(DATEPART(YY, @datecol) AS CHAR(4));
-- once you have the @WeekNum and @YearNum set, the following calculates the date range.
SELECT DATEADD(wk, DATEDIFF(wk, 6, '1/1/' + @YearNum) + (@WeekNum-1), 6) AS StartOfWeek;
SELECT DATEADD(wk, DATEDIFF(wk, 5, '1/1/' + @YearNum) + (@WeekNum-1), 5) AS EndOfWeek;
Solution 3
To answer your question:
--CHANGE A WEEK NUMBER BACK INTO A DATE FOR THE FIRST DATE OF THE WEEK
DECLARE @TaskWeek INT = 17
DECLARE @TaskYear INT = 2013
SELECT DATEADD(WEEK, @TaskWeek - 1,DATEADD(dd, 1 - DATEPART(dw, '1/1/' + CONVERT(VARCHAR(4),@TaskYear)), '1/1/' + CONVERT(VARCHAR(4),@TaskYear)))
Solution 4
If your week starts from Monday (on SQL Server 2008)
select datecol,
DATEPART(ISOWK, datecol) as week,
((DATEPART(dw, datecol)+5)%7)+1 as weekday,
(DATEADD(dd, -((DATEPART(dw, datecol)+5)%7), datecol)) as Monday,
(DATEADD(dd, -((DATEPART(dw, datecol)+5)%7)+6, datecol)) as Sunday
Solution 5
How about a function that jumps to the week before that week number and then steps through the next few days until the week number changes (max 7 steps), returning the new date?
CREATE FUNCTION dbo.fnGetDateFromWeekNo
(@weekNo int , @yearNo int)
RETURNS smalldatetime
AS
BEGIN
DECLARE @tmpDate smalldatetime
set @tmpdate= cast(cast (@yearNo as varchar) + '-01-01' as smalldatetime)
-- jump forward x-1 weeks to save counting through the whole year
set @tmpdate=dateadd(wk,@weekno-1,@tmpdate)
-- make sure weekno is not out of range
if @WeekNo <= datepart(wk,cast(cast (@yearNo as varchar) + '-12-31' as smalldatetime))
BEGIN
WHILE (datepart(wk,@tmpdate)<@WeekNo)
BEGIN
set @tmpdate=dateadd(dd,1,@tmpdate)
END
END
ELSE
BEGIN
-- invalid weeknumber given
set @tmpdate=null
END
RETURN @tmpDate
END
Jason
Updated on January 29, 2021Comments
-
Jason over 3 years
In Microsoft SQL Server, I have a week number
(from DATEPART(wk, datecol))
But what I would like to do is turn this back into the date span for that week.
For example,
SELECT DATEPART(wk, GETDATE())
yields 10
. I would like to derive3/1/2009
and3/7/2009
from this number.Is this possible?
-
jennykwan over 15 yearsUm, DATECOL is the user-defined column in the example. Quassnoi's code only relies on "DATEPART(weekday,", which is available on 2000.
-
sebacipo over 10 yearsGreat! but i ´ve to edit set @tmpdate=dateadd(wk,@weekno-2,@tmpdate)
-
TessellatingHeckler over 10 yearsNB. This does not work if you change the week starting day, as claimed. The default starting weekday is 7 (Sunday) and this code calculates assuming the current day is always <= 7. If you set the starting weekday to 1 (Monday), this code hits negative numbers and ends up showing you wrong dates from the week before.
-
TessellatingHeckler over 10 yearsNB. For similar reasons, I think it won't work at the start or end of a year either - saying week 1 began sometime in December of the previous year, or week 52 might end sometime in January the following year.
-
Ben over 9 yearsCould you explain what the 6 and 5 are for in the DATEDIFF?
-
ZagNut about 9 years@BenAdler doing
select cast(0 as datetime)
returns1900-01-01 00:00:00.000
, so 5 and 6 must be 5 and 6 days from jan 1, 1900 respectively. jan 7 is the first sunday of the year 1900. rest of calc is better shown than explained:select datediff(wk, 6, '1/1/2015');
returns 5999 weeks since 1/7/1900select datepart(wk, '6/23/2015');
returns 26 weeks since 1/1/2015select dateadd(wk, 5999 + (26 - 1), 6);
returns: 2015-06-21 00:00:00.000, which is exactly 6024 weeks since the first sunday of year 1900, ergo also a sunday -
Rob almost 9 yearsThe last two lines where exactly what we needed! Only changed the 5 and 6 because the weeks are starting on monday and ending on sunday here. DATEADD(wk, DATEDIFF(wk, 6, '1/1/' + CAST(pr.Jaar as nvarchar)) + (pr.Week-1), 7) AS StartOfWeek, DATEADD(wk, DATEDIFF(wk, 5, '1/1/' + CAST(pr.Jaar as nvarchar)) + (pr.Week-1), 6) AS EndOfWeek
-
Ian over 7 yearsSebacipo was right as the first day of Week 1 may be in December of the previous year. Also, the validation is incorrect if the last day in the year is week 1. Thanks for sharing though....this is perfect, with a few tweeks
-
ZenoArrow over 2 yearsJust a quick note to add to this, the start_weekday integer value of 0 is interpreted as a date (1900-01-01) when used in the DATEADD function. Similarly, the end_weekday integer value of 6 is interpreted as a date (1900-01-07) when used in the DATEADD function. The DATEADD function is applying a week offset to these base values.