Date Value from a week number in SQL

Question

Possible Duplicate:
Get dates from a week number in T-SQL

How do I get the date value if I have a week number in SQL Query.

Like if I pass 26, it should give me 06/24/2012. If I pass 27, I should get 07/01/2012

Any help will be appreciated :)

Sots

I'm assuming you're also passing year, or will it always be the 'current' year? And do you want the ISO week-number, or weeks since January 1st? — Clockwork-Muse, Jun 26 '12 at 23:06
This question is just noise without naming a particular RDBMS or otherwise specifying the purpose. Date functions are hardly DB agnostic. — Erwin Brandstetter, Jun 26 '12 at 23:57

Kevin Aenmey · Answer 1 · 2012-06-26T23:19:51.223

In SQL Server

DECLARE @StartDate DATE, @WeekVal INT
SET @WeekVal = 26 -- Set the week number
SET @StartDate = DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) -- Start of current year

;WITH cte AS (
    SELECT @StartDate AS DateVal, DATEPART(wk, @StartDate) AS WeekVal, 1 AS RowVal
    UNION ALL
    SELECT DATEADD(d, 1, DateVal),  DATEPART(wk, DATEADD(d, 1, DateVal)), RowVal + 1 
    FROM cte WHERE RowVal < 365
)
SELECT MIN(DateVal) StartOfWeek
FROM cte
WHERE WeekVal = @WeekVal
OPTION (MAXRECURSION 365);

score 0 · Answer 2 · answered Jun 26 '12 at 23:14

0

SELECT DATEADD(week, n, '11/25/2011');

with n being the week number

answered Jun 26 '12 at 23:14

Eugen Rieck

64,175
10
70
92

score 0 · Answer 3 · answered Jun 26 '12 at 23:14

0

If this doesn't work, try using WEEK() instead of WEEKOFYEAR().

CURDATE() - INTERVAL WEEKDAY(CURDATE()) DAY + INTERVAL (WEEKNO - WEEKOFYEAR(CURDATE())) WEEK

answered Jun 26 '12 at 23:14

Neil

54,642
8
60
72

score 0 · Answer 4 · answered Jun 27 '12 at 02:12

This gives you the start and end dates of the week. [For SQL Server]

Declare @week integer set @week = 26
Declare @Year Integer Set @Year = year(getdate())
declare @date datetime

-- ------------------------------------
Set @date = DateAdd(day, 0, 
            DateAdd(month, 0, 
            DateAdd(Year, @Year-1900, 0)))

set @date = Dateadd(week, @week-1, @date)

select @date startweek, DATEADD (D, -1 * DatePart (DW, @date) + 7, @date) endweek

This was the result from it:

startweek               endweek
----------------------- -----------------------
2012-07-01 00:00:00.000 2012-07-07 00:00:00.000

(1 row(s) affected)

Date Value from a week number in SQL

4 Answers4