Get dates from a week number in T-SQL

Jason_72593 0
Viewed 167859

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?

date tsql sql-server
20 Answers

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

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

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

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
user_4957360 
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
user_104232570
Related