Get First Day Of Week T-sql

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.

Solution 1:

DECLARE@YEARint=2020;
DECLARE@WEEKSTOADDint=6;
SET DATEFIRST 1;

SELECT 
        DATEADD(day, 
                1-  DATEPART(dw,DATEADD(week,@WEEKSTOADD,cast(cast(@YEARasvarchar(4)) +'0101'asdate))), 
                DATEADD(week,@WEEKSTOADD,cast(cast(@YEARasvarchar(4)) +'0101'asdate)))

Solution 2:

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() -30as Datum
  unionallselect DateAdd( day, 1, Datum )
    from SampleData
    where Datum < GetDate() )
select Datum,
  -- 1 = Monday through 7 = Sunday.
  ( @@DateFirst+ DatePart( weekday, Datum ) -2 ) %7+1as WeekDay,
  -- Date of Monday in the week of the supplied date.Cast( DateAdd( day, - ( @@DateFirst+ DatePart( weekday, Datum ) -2 ) %7, Datum ) asDate ) as Monday
  from SampleData;

Solution 3:

As per sample data you need substring() :

select t.*, substring(datnum, charindex(' ', datnum) +1, 10) as dt
fromtable t
where t.week =6;