I get a date like @periodId int = 201801, where 2018 is the year and 01 is the month. This data is converted to datetime format as follows.

declare @periodIdDatetimeFormat datetime select @periodIdDatetimeFormat = convert(varchar(4),@periodId/100)+ '-'+convert(varchar(2),@periodId%100)+'-'+'01'

The result is a date like "2018-01-01". It is necessary to display all the weeks of the given month in the format 01.01.2018-07.01.2018, with the first day of the week should be Thursday and last Friday (01.01-Thu, 07.01-Fri). Is there any way to do this using tsql?

  • one
    DATEPART () and DATEADD () help you - Nick Proskuryakov
  • And what about weeks that start or end in another month? And what version of SQL Server? - Denis Rubashkin
  • one
    In general, select a real month and add to the question the result you want to receive, because the "week" that starts on Thursday and ends on Friday is not a week, but 2 or 9 days and this `(01.01-Th , 07.01-Fri) `cannot be in the same month - Denis Rubashkin
  • Yes you are right. This is the essence, if the 1st day of the month falls, for example (as in April 2019 on Monday), then I need a week from Friday of the previous month (March 29) to Thursday of April (29.03-4.04). Maybe I was not quite right in expressing the question - sanjoyz

1 answer 1

I found this way

  DECLARE @periodId DATETIME set language russian SET @periodId = '2019-01-04' declare @firstWeekOfMonth datetime select @firstWeekOfMonth = (case when datepart(WEEKDAY,@periodId) < 5 then dateadd(dd,-(datepart(weekday,@periodId)+2),@periodId) when datepart(WEEKDAY,@periodId) = 5 then dateadd(dd,0,@periodId) else dateadd(dd,5-(datepart(weekday,@periodId)),@periodId) end) select @firstWeekOfMonth