SQL Server : get date with inparameters year,week,weekday

Question

I have searched and have yet to find this little helpful snippet.

I want to input...

Year (2014)
Weeknumber (2)
Weekday (2 = Tuesday, in my case)

Expected result: 2014-01-07 (seventh of January)

And get the full date in return, anyone?

EDIT: My server is SQL 2008

The finished code thanks to all!.

declare @year int = 2014
declare @weeknr int = 2
declare @daynroffset int = 2


SELECT 
  DATEADD(DAY,+ (@daynroffset-1),
    DATEADD(DAY,-DATEPART(DW,CAST('1/1/' + cast(@year as varchar) AS Date))+2,DATEADD(WK,@weeknr-    1,CAST('1/1/' + cast(@year as varchar) AS Date)))
  )

See http://stackoverflow.com/questions/607817/get-dates-from-a-week-number-in-t-sql — Raj, Oct 27 '14 at 18:13
SQL Server **2012** and newer have a [`GetDateFromParts`](http://msdn.microsoft.com/en-us/library/hh213228.aspx) function — marc_s, Oct 27 '14 at 18:14
@KekuSemau, outch, thanks you are right Weeknumber / Weekday — bummi, Oct 27 '14 at 18:35
All answers has incorrect result in 2016 year for SQL 2008. Eg. Input data: y = 2016, w = 22, d = 2. Should be 2016-05-31, but result is 2016-05-24. Please somebody explain and provide corrected code. — Andrey Davydenko, Jun 06 '16 at 09:28
@AndreyDavydenko: The method of week counting isn't very well defined. There is an ISO standard though, which SQL Server does *not* use by default. If you want to use ISO-weeks, you can add a correction factor by using this on the date calculated above: `declare @isowk_offset int = datepart(WK,@date)-datepart(isowk,@date) set @date = DATEADD(wk, @isowk_offset,@date)`. This is probably not fully sufficient around year change though. (week 1 vs week 52 or 53). — TToni, Jun 06 '16 at 09:43

TToni · Answer 1 · 2016-06-07T11:28:22.320

The other answers (so far) use SQL Server default mechanisms to determine week and day of week. In this case the current language setting determines the day of the week (through the @@DATEFIRST setting) and the DATEPART(wk uses Jan 1st as the fixed date contained in week 1.

To get a deterministic answer independent of the language setting one can use the ISO 8601 week standard which starts a week on Mondays and where the first week always contains Jan 4th.

This code determines the date based on ISO weeks:

declare @year int = 2016
declare @isoweek int = 22
declare @isoday int = 2

-- ISO-WEEK 1 always contains 4th Jan, so let's use this as a base
declare @date datetime = cast(cast(@year as varchar(4)) + '-01-04T12:00:00' as datetime)

-- Offset the wanted DayOfWeek versus our base date
-- We also set DATEFIRST temporarily because it affects DayOfWeek
-- ISO-Weeks always start on Monday
declare @datefirst int = @@DATEFIRST
SET DATEFIRST 1
declare @offset int = datepart(dw, @date) - 1
SET DATEFIRST @datefirst

-- Add given day and week to basedate
set @date = dateadd(day, @isoday - 1 - @offset, dateadd(wk, @isoweek - 1, @date))

print @date

Just one thing to add - construction `cast(cast(@year as varchar(4)) + '-01-04' as datetime)` depending on the settings. It can be not the 4 of JAN, but the 1 of APR. The rest is good! — Andrey Davydenko, Jun 07 '16 at 08:15
@AndreyDavydenko: Thanks for the bounty! I had the impression that using the format `yyyy-mm-dd` would be safe, but you are right, it isn't. However, the full ISO 8601 format should be safe, so I will update the answer accordingly. — TToni, Jun 07 '16 at 11:26
TY! I used `CONVERT` with a style: `CONVERT(DATETIME, CONVERT(VARCHAR(4), @year) + '0104', 112)` — Andrey Davydenko, Jun 08 '16 at 07:44

score 3 · Answer 2 · edited Jun 20 '20 at 09:12

3

CODE:

2012+:
DATEADD(DAY,-DATEPART(DW,DATEFROMPARTS("YEAR",1,1))+1+"DAY OF WEEK",DATEADD(WK,"WEEK NUMBER"-1,DATEFROMPARTS(2014,1,1)))

2008+:
SELECT DATEADD(DAY,-DATEPART(DW,CAST(CONCAT('1/1/',"YEAR") AS Date))+1+"DAY OF WEEK",DATEADD(WK,"WEEK NUMBER"-1,CAST(CONCAT('1/1/',"YEAR") AS Date)))

Simply substitue the values where necessary.

This will work for any date.

edited Jun 20 '20 at 09:12

Community

1
1

answered Oct 27 '14 at 18:53

Mr. Mascaro

2,693
1
11
18

Great... but dont think 2008 have datefromparts as function – freand Oct 27 '14 at 19:00
Then you could substitue a `CONCAT` inside a `CAST` for that. – Mr. Mascaro Oct 27 '14 at 19:03
Yup... aside from the missing concat function... think i have it tho... used the "+" sign instead, thanks for getting me on my way... ill post the result here – freand Oct 27 '14 at 19:26
1

@freand, check my updated answer. Should be just what you need. – Mr. Mascaro Oct 27 '14 at 19:28
incorrect result in 2016 year for SQL 2008. Eg. Input data: y = 2016, w = 22, d = 2. Should be 2016-05-31, but result is 2016-05-24. Please somebody explain and provide corrected code. – Andrey Davydenko Jun 06 '16 at 09:31

SmartDev · Answer 3 · 2014-10-27T19:35:32.627

1

declare @year int = 2014
declare @week int = 2
declare @day int = 2

declare @date datetime = cast(cast(@year as varchar(20)) + '-01-01' as datetime)
declare @offset int = datepart(dw, @date) - 1
set @date = dateadd(day, @day - @offset, dateadd(ww, @week - 1, @date))

print @date

edited Oct 27 '14 at 19:35

answered Oct 27 '14 at 19:12

SmartDev

2,802
1
17
22

Does not work with SQL Server 2008. `DATEFROMPARTS` appears in 2012. – Mr. Mascaro Oct 27 '14 at 19:30

Brandon · Answer 4 · 2014-10-27T19:36:42.653

This may have issues near the year boundary, but it works for the example data given. You may want to add further validations. I've broken down each step of the datetime manipulation into a new field, so you can see it being constructed

2008

DECLARE @Year INT = 2014
DECLARE @WeekNum INT = 2
DECLARE @WeekDay INT = 2

SELECT
    BaseDate = CAST( @year AS VARCHAR(4) )
  , RoundToWeekStart = DATEADD(WEEK, DATEDIFF(WEEK, 0, CAST( @year AS VARCHAR(4) )), 0) -- Will be a Monday
  , AddWeeksToRoundedDate = DATEADD(WEEK, @WeekNum - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, CAST( @year AS VARCHAR(4) )), 0) )
  , AddWeekDay = DATEADD( DAY, @WeekDay - 1, DATEADD(WEEK, @WeekNum - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, CAST( @year AS VARCHAR(4) )), 0) ) )

2012+

DECLARE @Year INT = 2014
DECLARE @WeekNum INT = 2
DECLARE @WeekDay INT = 2

SELECT
    BaseDate = DATEFROMPARTS(@Year, 1, 1)
  , RoundToWeekStart = DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEFROMPARTS(@Year, 1, 1)), 0) -- Will be a Monday
  , AddWeeksToRoundedDate = DATEADD(WEEK, @WeekNum - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEFROMPARTS(@Year, 1, 1)), 0) )
  , AddWeekDay = DATEADD( DAY, @WeekDay - 1, DATEADD(WEEK, @WeekNum - 1, DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEFROMPARTS(@Year, 1, 1)), 0) ) )

Does not work with SQL Server 2008. `DATEFROMPARTS` appears in 2012. — Mr. Mascaro, Oct 27 '14 at 19:31

SQL Server : get date with inparameters year,week,weekday

4 Answers4

CODE: