First day and last day of week from week number

• SQLArch

  Ten Centuries

  Points: 1322

  More actions

  September 27, 2005 at 2:48 pm

  #102824

  I want the first day and last day of week and then look at last day of week to compare with cureent date, if passed then do calculations.

  From date I can get by this:

  DECLARE @Date datetime

  SET @Date = getdate()

  SET DATEFIRST 1

  SELECT @@DATEFIRST AS '1st Day', DATEPART(dw, @Date ) AS 'Today'

   , dateadd(dd, 1 - DATEPART(dw, @Date ), @Date -7)  as FirstDateofWeek

   , dateadd(dd, 7- DATEPART(dw, @Date ), @Date -7)  as LastDateofWeek

   , cast(datepart(wk, @Date) as varchar) as weeknumber

  But, I don't have date  and have only week number 40 ( no date), how do I get first day and the last day of week number 40?

   

• Carl Federl

  One Orange Chip

  Points: 25384

  More actions

  September 27, 2005 at 3:27 pm

  #593487

  To get Monday's date giving the number of weeks since the beginning of the yearm, try

  Declare @Weeks integer

  , @YearStart datetime

  set @Weeks = 37

  set @YearStart = '2003-01-01'

  select MondayOfWeek = WeekDate

  + CASE DATEPART(dw, WeekDate)

  WHEN 1 then +1 -- Sun

  WHEN 2 then 0 -- Mon

  WHEN 3 then -1 -- Tue

  WHEN 4 then -2 -- Wed

  WHEN 5 then -3 -- Thu

  WHEN 6 then -4 -- Fri

  WHEN 7 then -5 -- Sat

  else null

  end

  FROM (select dateadd(wk, @Weeks , @YearStart))

  as W (WeekDate)

  SQL = Scarcely Qualifies as a Language