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@Weeksinteger

  ,@YearStartdatetime

  set@Weeks= 37

  set@YearStart= '2003-01-01'

  selectMondayOfWeek = 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