Get dates from a week number in T-SQL

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 derive 3/1/2009 and 3/7/2009 from this number.

Is this possible?

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

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;

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

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

SELECT DATECOL - DATEPART(weekday, DATECOL), DATECOL - DATEPART(weekday, DATECOL) + 7

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

I've taken elindeblom's solution and modified it - the use of strings (even if cast to dates) makes me nervous for the different formats of dates used around the world. This avoids that issue.

While not requested, I've also included time so the week ends 1 second before midnight:

    DECLARE @WeekNum INT = 12,
        @YearNum INT = 2014 ;

    SELECT  DATEADD(wk,
                    DATEDIFF(wk, 6,
                             CAST(RTRIM(@YearNum * 10000 + 1 * 100 + 1) AS DATETIME))
                    + ( @WeekNum - 1 ), 6) AS [start_of_week],
            DATEADD(second, -1,
                    DATEADD(day,
                            DATEDIFF(day, 0,
                                     DATEADD(wk,
                                             DATEDIFF(wk, 5,
                                                      CAST(RTRIM(@YearNum * 10000
                                                                 + 1 * 100 + 1) AS DATETIME))
                                             + ( @WeekNum + -1 ), 5)) + 1, 0)) AS [end_of_week] ;

Yes, I know I'm still casting but from a number. It "feels" safer to me.

This results in:

    start_of_week           end_of_week
    ----------------------- -----------------------
    2014-03-16 00:00:00.000 2014-03-22 23:59:59.000

Another way to do it:

declare @week_number int;
declare @start_weekday int = 0 -- Monday
declare @end_weekday int = 6 -- next Sunday

select @week_number = datediff(week, 0, getdate())

select 
    dateadd(week, @week_number, @start_weekday) as WEEK_FIRST_DAY, 
    dateadd(week, @week_number, @end_weekday) as WEEK_LAST_DAY

Explanation:

• @week_number is computed based on the initial calendar date '1900-01-01'. Replace getdate() by whatever date you want.
• @start_weekday is 0 if Monday. If Sunday, then declare it as -1
• @end_weekday is 6 if next Sunday. If Saturday, then declare it as 5
• Then dateadd function, will add the given number of weeks and the given number of days to the initial calendar date '1900-01-01'.

This should work regardless of @@DATEFIRST

ALTER FUNCTION dbo.DEV_VW_WeekSerial
	(@YearNum int,
	@WeekNum int,
	@DayNum int)
	RETURNS Date AS

	BEGIN
	
		DECLARE @FirstDayYear As Date;

		SET @FirstDayYear='01/01/' + CAST(@YearNum As varchar)

		RETURN dateadd(d,(@DayNum-datepart(weekday,@FirstDayYear)),dateadd(week, @WeekNum-1,@FirstDayYear))

	END

dateadd(
  dd, 
  datepart(wk, @Date)*7, 
  convert(smalldatetime, convert(char,year(max(@Date)))+convert(char, '-01-01'))
)-1 

Here you just have to pass year and week number.

DECLARE @Year VARCHAR(4)

SET @Year= '2012'

DECLARE @FirstDate DATETIME

SET @FirstDate = (SELECT DATEADD(dd,1,(SELECT DATEADD(wk,DATEPART(wk,GETDATE())-1,Convert(DAteTime,'01-01-' + @Year))))
				 )
DECLARE @LastDate DATETIME

SET @LastDate =(SELECT DATEADD(dd,4,@FirstDate))

SELECT @FirstDate
	   ,@LastDate

Give it @Year and @Week, return first date of that week.

Declare @Year	int
,@Week int
,@YearText varchar(4)

set @Year = 2009
set @Week = 10

set @YearText = @Year

print dateadd(day
             ,1 - datepart(dw, @YearText + '-01-01')
                + (@Week-1) * 7
             ,@YearText + '-01-01')

I just incorporated the SELECT with a CASE statement (For my situation Monday marked the first day of the week, and didn't want to deal with the SET DATEFIRST command:

CASE DATEPART(dw,<YourDateTimeField>)
   WHEN 1 THEN CONVERT(char(10), DATEADD(DD, -6, <YourDateTimeField>),126) +  ' to ' + CONVERT(char(10), <YourDateTimeField>,126)
   WHEN 2 THEN CONVERT(char(10), <YourDateTimeField>,126) +  ' to ' + CONVERT(char(10), DATEADD(DD, 6, <YourDateTimeField>),126)
   WHEN 3 THEN CONVERT(char(10), DATEADD(DD, -1, <YourDateTimeField>),126) +  ' to ' + CONVERT(char(10), DATEADD(DD, 5, <YourDateTimeField>),126)
   WHEN 4 THEN CONVERT(char(10), DATEADD(DD, -2, <YourDateTimeField>),126) +  ' to ' + CONVERT(char(10), DATEADD(DD, 4, <YourDateTimeField>),126)
   WHEN 5 THEN CONVERT(char(10), DATEADD(DD, -3, <YourDateTimeField>),126) +  ' to ' + CONVERT(char(10), DATEADD(DD, 3, <YourDateTimeField>),126)
   WHEN 6 THEN CONVERT(char(10), DATEADD(DD, -4, <YourDateTimeField>),126) +  ' to ' + CONVERT(char(10), DATEADD(DD, 2, <YourDateTimeField>),126)
   WHEN 7 THEN CONVERT(char(10), DATEADD(DD, -5, <YourDateTimeField>),126) +  ' to ' + CONVERT(char(10), DATEADD(DD, 1, <YourDateTimeField>),126)
   ELSE 'UNK'
END AS Week_Range

The most votes answer works fine except the 1st week and last week of year. When datecol value is '2009-01-01', the result will be 01/03/2009 and 12/28/2008.

My solution:

DECLARE @Date date = '2009-03-01', @WeekNum int, @StartDate date;
SELECT @WeekNum = DATEPART(WEEK, @Date);
SELECT @StartDate = DATEADD(DAY, -(DATEPART(WEEKDAY, DATEADD(YEAR, DATEDIFF(YEAR, 0, @Date), 0)) + 6), DATEADD(YEAR, DATEDIFF(YEAR, 0, @Date), 0));
SELECT CONVERT(nvarchar, CASE WHEN @WeekNum = 1 THEN CAST(DATEADD(YEAR, DATEDIFF(YEAR, 0, @Date), 0) AS date) ELSE DATEADD(DAY, 7 * @WeekNum, @StartDate) END, 101) AS StartOfWeek
	  ,CONVERT(nvarchar, CASE WHEN @WeekNum = DATEPART(WEEK, DATEADD(DAY, -1, DATEADD(YEAR, DATEDIFF(YEAR, 0, @Date) + 1, 0))) THEN DATEADD(DAY, -1, DATEADD(YEAR, DATEDIFF(YEAR, 0, @Date) + 1, 0)) ELSE DATEADD(DAY, 7 * @WeekNum + 6, @StartDate) END, 101) AS EndOfWeek;

This will display 01/01/2009 and 01/03/2009 for the 1st week, and display 03/01/2009 and 03/07/2009 for the 10th week.

I think this would be what you want exactly. You can replace the variables with their expressions as you wish.

declare @IntWeek as varchar(20)
SET @IntWeek = '201820'

SELECT 
    DATEADD(wk, DATEDIFF(wk, @@DATEFIRST, LEFT(@IntWeek,4) + '-01-01') +
    (cast(RIGHT(@IntWeek, 2) as int) -1), @@DATEFIRST) AS StartOfWeek

SELECT DATEADD(week, @weekNumber - 1, DATEADD(DAY, @@datefirst - DATEPART(weekday, CAST(YEAR(GETDATE()) AS VARCHAR) + '-01-01') - 6, CAST(YEAR(GETDATE()) AS VARCHAR) + '-01-01'))

DECLARE @dayval int,
 @monthval int,
 @yearval int

SET @dayval = 1
SET @monthval = 1
SET @yearval = 2011


DECLARE @dtDateSerial datetime

		SET @dtDateSerial = DATEADD(day, @dayval-1,
								DATEADD(month, @monthval-1,
									DATEADD(year, @yearval-1900, 0)
								)
							)
							
DECLARE @weekno int
SET @weekno = 53


DECLARE @weekstart datetime
SET @weekstart = dateadd(day, 7 * (@weekno -1) - datepart (dw, @dtDateSerial), @dtDateSerial)

DECLARE @weekend datetime
SET @weekend = dateadd(day, 6, @weekstart)

SELECT @weekstart, @weekend

Answer:

select  DateAdd(day,-DATEPart(DW,<Date>), <Date>) [FirstDayOfWeek] ,DateAdd(day,-DATEPart(DW,<Date>)+6, <Date>) [LastDayOfWeek]
FROM <TABLE>

This works for me:

select 
	convert(varchar(50), dateadd(dd, - datepart(dw, DATECOL) + 1, DATECOL), 101),
	convert(varchar(50), dateadd(dd, - datepart(dw, DATECOL) + 7, DATECOL), 101)

I didn't take the time to test out every answer on here, but nothing seems as simple and as efficient as this:

DECLARE @WeekNum int
DECLARE @YearNum char(4)

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