Date Question !!!

• Krishna Kumar Subramaniam

  Valued Member

  Points: 65

  More actions

  August 31, 2004 at 3:45 am

  #83716

  I have the week number for a year, how do I find out the dates for that week.

  Eg: week 35 for this yr is current week of august.  

  So when I enter 35 as input it should give me 21-Aug-2004 to 28-Aug-2004.  

  Can anybody help me out with this one .....

  thankz in advance ....

   

• babuganesh2000

  Hall of Fame

  Points: 3123

  More actions

  August 31, 2004 at 4:56 am

  #521186

  I will give the framework to do that. There might be some syntax error also. Pls do adjust.

  CREATE FUNCTION First_Last_Week_Date(@Weekno int, @Year int)

  RETURNS varchar

  AS

  BEGIN

  DECLARE @ReturnString varchar(50)

  DECLARE @FirstDayOfWeek datetime

  DECLARE @LastDayOfWeek datetime

  DECLARE @Year varchar(25)

  SET @Year = '1/1/' + @Year

  SET @FirstDayOfWeek = DATEADD(wk, @Weekno, @Year)

  SET @LastDayOfWeek = DATEADD(dd, 7, @FirstDayOfWeek)

  SET @ReturnString = @FirstDayOfWeek + @LastDayOfWeek

  RETURN (@ReturnString)

  END

  Thanks,

  Ganesh

• SQLBill

  SSC Guru

  Points: 51440

  More actions

  August 31, 2004 at 12:09 pm

  #521268

  Krishna,

  Ganesh's suggestion is excellent with two corrections.

  Let's say you are trying this for week 35.

  1/1/2004 is week 1. So if you add @weekno (35) to it you will actually be getting week 36.

  Adjust this:

  SET @FirstDayOfWeek = DATEADD(wk, @Weekno, @Year)

  to be:

  SET @FirstDayOfWeek = DATEADD(wk, (@Weekno -1), @Year)

  The second correction is that when you take the first day of the week (datepart 1) and add 7 to it, you get 8 which is back to datepart 1. So, fix this:

  SET @LastDayOfWeek = DATEADD(dd, 7, @FirstDayOfWeek)

  to be:

  SET @LastDayOfWeek = DATEADD(dd, 6, @FirstDayOfWeek)

  Test both Ganesh's before and after the change/corrections and check the results. By the way, you need to make sure that your system is set for the proper first day of the week. Sunday should be the first day (Datepart 1). To check run this:

  SELECT DATEPART(dw, getdate())

  For 31 Aug it should return 3 (third day of the week). If not you can use SET DATEFIRST to get the correct date. Refer to the BOL.

  -SQLBill

  BOL=Books OnLine=Microsoft SQL Server's HELP

  Installed as part of the Client Tools

  Found at Start>Programs>Microsoft SQL Server>Books OnLine

• rockmoose

  SSCarpal Tunnel

  Points: 4601

  More actions

  August 31, 2004 at 4:19 pm

  #521287

  Actually,

  DATEADD(WEEK,x,somedate) Just adds 7*x days to the given date...

  Not very useful at all.

  The above functions don't give the correct results.

  Try:

  -- Input Variables

  DECLARE @week INT, @year INT

  SELECT @week = 35, @year = YEAR(GETDATE())

  -- Calculation

  DECLARE @hitdate DATETIME, @DW INT

  SET @hitdate = DATEADD(YEAR,@year-1900,0) + (@week-1)*7

  SET @DW = DATEPART(dw,@hitdate)

  -- Result

  SELECT DATEADD(DAY,-@dw,@hitdate), DATEADD(DAY,7-@dw,@hitdate)

  SELECT CAST(DATEADD(DAY,-@dw,@hitdate) AS VARCHAR) + ' to ' + CAST(DATEADD(DAY,7-@dw,@hitdate) AS VARCHAR)

  I can say right now - I am not sure how You count the weeks when one year turns into another e.g: 2003-2004

  /rockmoose

  
  You must unlearn what You have learnt