calculate and display week between two dates

• abhas

  SSCarpal Tunnel

  Points: 4681

  More actions

  November 4, 2015 at 12:21 am

  #321897

  Hi,

  I want to display week between two dates as below.

  requirement is as:

  suppose there are two dates.(which will comes dynamically, so no. of weeks varied)

  10/20/2015 and 01/01/2016

  Now between this two dates, i want to calculate number of weeks on another date which is coming from table.

  say for example the column date is coming as 10/23/2015 then it will fall in week-1

  same way if 11/01/2015 falls in week2.

  please let me know any idea.

  Thanks,

  Abhas.

• Umer Akram

  SSC-Insane

  Points: 22209

  More actions

  November 4, 2015 at 1:03 am

  #1837534

  you can get weeks between two dates. but what i didn't understand is the statement below

  Now between this two dates, i want to calculate number of weeks on another date which is coming from table.

  what exactly the problem you are facing please share some details.

• abhas

  SSCarpal Tunnel

  Points: 4681

  More actions

  November 4, 2015 at 2:03 am

  #1837543

  Thanks twin

  i resolved the issue. Same your solution worked.

  DECLARE @StartDate SMALLDATETIME = '1/11/2016'

  declare @tempdat table(repdate smalldatetime)

  insert into @tempdat values ('10/26/2015')

  insert into @tempdat values ('10/29/2015')

  insert into @tempdat values ('11/1/2015')

  insert into @tempdat values ('11/27/2015')

  insert into @tempdat values ('11/25/2015')

  insert into @tempdat values ('11/20/2015')

  insert into @tempdat values ('11/10/2015')

  insert into @tempdat values ('11/10/2015')

  insert into @tempdat values ('11/11/2015')

  insert into @tempdat values ('11/11/2015')

  SELECT ABS(DATEDIFF(week,@StartDate,repdate)) from @tempdat

• Jeff Moden

  SSC Guru

  Points: 1004401

  More actions

  November 4, 2015 at 6:30 am

  #1837631

  The "WEEK" date part is based on the "DATEFIRST" server setting. If you change servers (especially multi-country) or if someone gets some sort of date religion and changes the setting, then the answer to your code will change. That means that your code may suffice but it's not bullet proof.

  I don't know how many times a day your code will run but using separate hardcoded inserts is a form of RBAR at it's worst.

  Tell us what day of the week a week starts on and whether or not the first and last weeks of the year are any different and lets do this the right way.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)