-3

How get first day of week (Monday) where week = 6 and year = 2020 I need get 10.02.2020

eg. week 1 in 2020 is date from 06.01.2020 - 12.01.2020 week 6 in 2020 is date from 10.02.2020 - 16.02.2020

user_odoo
  • 2,284
  • 34
  • 55
  • 1
    with reference to which week? – sveer Feb 20 '20 at 14:14
  • What data are you showing? – Gordon Linoff Feb 20 '20 at 14:17
  • Does this answer your question? [Get dates from a week number in T-SQL](https://stackoverflow.com/questions/607817/get-dates-from-a-week-number-in-t-sql) – Eric Brandt Feb 20 '20 at 14:17
  • @sveer I'm update question. – user_odoo Feb 20 '20 at 14:18
  • Your question is not clear. Are you trying to `SELECT` the value `10.02.2020` from the `datum` column in the table shown, or are you trying to arrive at the date `10.02.2020` using code, given only the week number and the year? Two _completely_ different questions. – Eric Brandt Feb 20 '20 at 14:24
  • This is best solved with a physical numbers/date table. Such a table is very handsome in a lot of situations...There are various examples to find, I post one [here](https://stackoverflow.com/a/32474751/5089204) myself. – Shnugo Feb 20 '20 at 14:25

3 Answers3

1
DECLARE @YEAR int = 2020;
DECLARE @WEEKSTOADD int = 6;
SET DATEFIRST 1;

SELECT 
        DATEADD(day, 
                1 -  DATEPART(dw,DATEADD(week,@WEEKSTOADD,cast(cast(@YEAR as varchar(4)) + '0101' as date))), 
                DATEADD(week,@WEEKSTOADD,cast(cast(@YEAR as varchar(4)) + '0101' as date)))
Cato
  • 3,652
  • 9
  • 12
  • Tips: `( @@DateFirst + DatePart( weekday, SampleDate ) - 1 ) % 7 + 1` will always return an integer from `1` to `7` with `1` corresponding to Sunday regardless of the setting of `DateFirst` or `Language`. And you may want to update your answer for modern version of SQL Server to use [`DateFromParts`](https://learn.microsoft.com/en-us/sql/t-sql/functions/datefromparts-transact-sql?view=sql-server-ver15). – HABO Feb 20 '20 at 14:36
  • which bit would you want to update for 'modern' SQL? Maybe create a date now available without using a string? I want 1 to represent a Monday though. – Cato Feb 20 '20 at 15:07
  • `( @@DateFirst + DatePart( weekday, SampleDate ) - 2 ) % 7 + 1` shifts the starting day to Monday. Yes, you shouldn't need to convert the year to prose, concatenate a few characters and analyze the result to get a `Date`. – HABO Feb 20 '20 at 15:25
1

The following code will get the date of Monday in the week of a given date regardless of the setting of DateFirst or Language:

Cast( DateAdd( day, - ( @@DateFirst + DatePart( weekday, Datum ) - 2 ) % 7, Datum ) as Date )

An example with sample data:

with SampleData as (
  select GetDate() - 30 as Datum
  union all
  select DateAdd( day, 1, Datum )
    from SampleData
    where Datum < GetDate() )
select Datum,
  -- 1 = Monday through 7 = Sunday.
  ( @@DateFirst + DatePart( weekday, Datum ) - 2 ) % 7 + 1 as WeekDay,
  -- Date of Monday in the week of the supplied date.
  Cast( DateAdd( day, - ( @@DateFirst + DatePart( weekday, Datum ) - 2 ) % 7, Datum ) as Date ) as Monday
  from SampleData;
HABO
  • 15,314
  • 5
  • 39
  • 57
0

As per sample data you need substring() :

select t.*, substring(datnum, charindex(' ', datnum) + 1, 10) as dt
from table t
where t.week = 6;
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52