Using datediff/dateadd function

• nehaCS

  Old Hand

  Points: 330

  More actions

  June 14, 2012 at 10:43 am

  #259779

  I need help on using date functions.

  I have to calculate the date on friday for past 8th week.

  Here is the scenario:

  My input is any date entered by user. If the user doesn't provide any date it should take getdate() by default.

  Now based on this it should go to the past 8th week and show the date on friday of that week.

  Example:

  take any date in current week.Let's say 6-13-2012

  Now I am calculating the weeks from previos week and going to 8th week it will be week starting on 4-16-2012.

  So my calculation should show result as 4-20-2012 and the formt I want is code 101

  I was doing like Dateadd(WW, -8, getdate())

  This gives the date in past 8th week but not friday.

• Lowell

  SSC Guru

  Points: 323518

  More actions

  June 14, 2012 at 10:55 am

  #1501181

  here's two examples of getting monday or friday of the current week.

  --Monday of the Current Week

  select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)

  --Friday of the Current Week

  select dateadd(dd,4,DATEADD(wk, DATEDIFF(wk,0,getdate()), 0))

  from there. you can modify it to add more weeks as your require.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 14, 2012 at 10:55 am

  #1501182

  Here is some code to look over and run. Any questions (and there should be if you don't understand anything), let us know.

  DECLARE @ThisDate DATETIME;

  SET @ThisDate = '20120613';

  SELECT

  @ThisDate,

  DATEADD(ww,-8,@ThisDate),

  DATEADD(ww,DATEDIFF(ww,0,ISNULL(@ThisDate,GETDATE())) - 8,4),

  DATEADD(ww,DATEDIFF(ww,'19000101',ISNULL(@ThisDate,GETDATE())) - 8, DATEADD(dd, 4, '19000101'));

  SET @ThisDate = null;

  SELECT

  @ThisDate,

  DATEADD(ww,-8,ISNULL(@ThisDate,GETDATE())),

  DATEADD(ww,DATEDIFF(ww,0,ISNULL(@ThisDate,GETDATE())) - 8,4),

  DATEADD(ww,DATEDIFF(ww,'19000101',ISNULL(@ThisDate,GETDATE())) - 8, DATEADD(dd, 4, '19000101'));

• nehaCS

  Old Hand

  Points: 330

  More actions

  June 14, 2012 at 11:25 am

  #1501194

  SELECT

  @ThisDate,

  DATEADD(ww,-8,@ThisDate),

  DATEADD(ww,DATEDIFF(ww,0,ISNULL(@ThisDate,GETDATE())) - 8,4),

  DATEADD(ww,DATEDIFF(ww,'19000101',ISNULL(@ThisDate,GETDATE())) - 8, DATEADD(dd, 4, '19000101'));

  So I understood the first part and half of second that u r using the isnull func and date diff.. I am getting the correct results too.. Thanks for the help.

  But why r u using the no 4 as last argument in datedd? Can u plz explain..

  Shouldn't the third argument be a date ?

  DATEADD(datepart,number,date)

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 14, 2012 at 11:41 am

  #1501198

  nehaCS (6/14/2012)

  ---

  SELECT

  @ThisDate,

  DATEADD(ww,-8,@ThisDate),

  DATEADD(ww,DATEDIFF(ww,0,ISNULL(@ThisDate,GETDATE())) - 8,4),

  DATEADD(ww,DATEDIFF(ww,'19000101',ISNULL(@ThisDate,GETDATE())) - 8, DATEADD(dd, 4, '19000101'));

  So I understood the first part and half of second that u r using the isnull func and date diff.. I am getting the correct results too.. Thanks for the help.

  But why r u using the no 4 as last argument in datedd? Can u plz explain..

  Shouldn't the third argument be a date ?

  DATEADD(datepart,number,date)

  If you look at these two pieces, you will see that they return the same value for the same input. The first is simply a short cut.

  DATEADD(ww,DATEDIFF(ww,0,ISNULL(@ThisDate,GETDATE())) - 8,4),

  DATEADD(ww,DATEDIFF(ww,'19000101',ISNULL(@ThisDate,GETDATE())) - 8, DATEADD(dd, 4, '19000101'));

  In the first, the 0 is the zero day or 1900-01-01. The 4 represents 4 days later or 1900-01-05.

• drew.allen

  SSC Guru

  Points: 76987

  More actions

  June 14, 2012 at 11:45 am

  #1501201

  Just to clarify, integers can be implicitly converted to date/time data. DATEADD() converts the supplied integer to the required datetime data type.

  The reason that 4 (1900-01-05) is used, is that it's a Friday, so adding whole weeks to it will also produce a Friday date.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• nehaCS

  Old Hand

  Points: 330

  More actions

  June 14, 2012 at 11:57 am

  #1501204

  Thanks all !!