How to get date from WeekNumber and WeekDay

Question

I have this dataset with WeekNo (Year and week) - WeekDayName which is the day there is data. I need to know exactly what date it is.

WeekNo  WeekDayName
------  -----------
201905  Mandag
201905  Mandag
201905  Mandag
201905  Tirsdag
201905  Tirsdag
201905  Tirsdag
201905  Onsdag
201905  Onsdag
201905  Onsdag
201905  Torsdag
201905  Torsdag
201905  Torsdag
201905  Fredag
201905  Fredag
201905  Fredag

Desired result:

WeekNo  WeekDayName Date
------  ----------- -----
201905  Mandag      2019-01-28
201905  Mandag      2019-01-28
201905  Mandag      2019-01-28
201905  Tirsdag     2019-01-29
201905  Tirsdag     2019-01-29
201905  Tirsdag     2019-01-29
201905  Onsdag      2019-01-30
201905  Onsdag      2019-01-30
201905  Onsdag      2019-01-30
201905  Torsdag     2019-01-31
201905  Torsdag     2019-01-31
201905  Torsdag     2019-01-31
201905  Fredag      2019-02-01
201905  Fredag      2019-02-01
201905  Fredag      2019-02-01

Ive tried this, but it only gives me the start of the week:

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

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

Probably best by first creating a `[Calendar]` table with all dates of the year(s), and extra fields that can't be calculated directly via [DATEPART](https://learn.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql). Then you can join your table to it. — LukStorms, Jan 30 '19 at 23:22
@LukStorms Great idea! Why didnt I think of that. That might be a more sustainable solution. Thanks — SqlKindaGuy, Jan 30 '19 at 23:23

Derviş Kayımbaşıoğlu · Accepted Answer · 2019-01-30T23:33:41.513

0

you may consider to have Calendar Table

After that you can easily get result

select date 
from Auxiliary.Calendar 
where Year = CAST(LEFT(@IntWeek,4) as int)
  and Week = CAST(RIGHT(@IntWeek,2) as int)
  and WeekDay = 'Mondag'

edited Jan 30 '19 at 23:33

answered Jan 30 '19 at 23:22

Derviş Kayımbaşıoğlu

28,492
4
50
72

Are you missing something? I need the dates. This just gives me one date, and basically the same result as i already have. – SqlKindaGuy Jan 30 '19 at 23:25
Yes excatly and daynumber. I dont know why I didnt came up with this, but its obviously much easier! So thanks again :) – SqlKindaGuy Jan 30 '19 at 23:30

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

0

Create a function that receives 2 parameters:

1.- your First day of the week

2.- the day name

The function must do IF

IF dayName = 'Sunday'
  Return same date
Else If dayName = 'Monday
  Return Data + 1 day

....

edited Jun 20 '20 at 09:12

Community

1
1

answered Jan 30 '19 at 23:38

Antonio Avndaño Duran

143
8

How to get date from WeekNumber and WeekDay

2 Answers2