title: What is the correct way to do date math in this TSQL Stored Procedure?
body: <p>Here is my business case: I need to, based on the user's input of date values for "BegDate" and "EndDate" also determine two other dates. Specifically, I need to know:</p>
  
  <pre><code>Week1Begin (which is the value they enter for "BegDate")
  Week2End (which is the value they enter for "EndDate")
  Week1End (which is the value they enter for "BegDate" + 6 days)
  Week2Begin (which is the value they enter for "BegDate" + 7 days)
  </code></pre>
  
  <p>IOW, if the user enters these values:</p>
  
  <pre><code>BegDate = 12/27/2015
  EndDate = 1/6/2016
  </code></pre>
  
  <p>...the values for the others need to be calculated as:</p>
  
  <pre><code>Week1End = 1/2/2016
  Week2Begin = 1/3/2016
  </code></pre>
  
  <p>I have a Stored Procedure which executes but returns all Nulls and 0s in the Summed columns, probably because the date math hasn't been included in the SP. Here is the SP as it is now:</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[duckbilledPlatypi]
      @Unit varchar(25),
      @BegDate datetime,
      @EndDate datetime
  AS
  
  DECLARE 
  @Week1End datetime,
  @Week2Begin datetime
  
  Select Ind.Description,
      @BegDate  BegDate,
      @Week1End Week1End,
      @Week1End Week2Begin,
      @EndDate EndDate,
      SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.QtyShipped ELSE 0 END) 
  Week1Usage,
      SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.QtyShipped ELSE 0 END) 
  Week2Usage,
      SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) 
  Week1Price,
      SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.Price ELSE 0 END) -
     SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.QtyShipped ELSE 0 END) 
  UsageVariance,
      SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.QtyShipped ELSE 0 END) 
   -
      SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) 
  PriceVariance,
      (SUM(CASE WHEN Ind.InvoiceDate BETWEEN @Week2Begin AND @EndDate THEN Ind.QtyShipped ELSE 0 
  END)  -
      SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END) )
      / 
  NULLIF(SUM(CASE WHEN Ind.InvoiceDate BETWEEN @BegDate AND @Week1End THEN Ind.Price ELSE 0 END),0) 
  PercentageOfPriceVariance
      From    InvoiceDetail Ind
      Where   Ind.Unit = @Unit
      AND Ind.InvoiceDate BETWEEN @BegDate AND @EndDate
      Group By Ind.Description
  </code></pre>
  
  <p>What do I need to add to perform the needed date math? Something like this:</p>
  
  <pre><code>. . .
  Select Ind.Description,
      @BegDate  BegDate,
      DATE_ADD(BegDate,INTERVAL 6 DAY) Week1End,
      DATE_ADD(BegDate,INTERVAL 7 DAY)  Week2Begin,
      @EndDate EndDate,
  . . .
  </code></pre>
  
  <p>or this:</p>
  
  <pre><code>. . .
  DECLARE 
  @Week1End datetime = DATE_ADD(BegDate,INTERVAL 6 DAY),
  @Week2Begin datetime = DATE_ADD(BegDate,INTERVAL 7 DAY)
  . . .
  </code></pre>
  
  <p>...or???</p>
  
  <h2>UPDATE</h2>
  
  <p>Okay, this:</p>
  
  <pre><code>DECLARE @Week1End datetime = DATEADD(Day, 6, @BegDate),
  @Week2Begin datetime = DATEADD(Day, 7, @BegDate);
  </code></pre>
  
  <p>...or this:</p>
  
  <pre><code>DECLARE @Week1End datetime = DATEADD(Day, 6, @BegDate),
  @Week2Begin datetime = DATEADD(Day, 7, @BegDate)
  </code></pre>
  
  <p>...or this:</p>
  
  <pre><code>DECLARE @Week1End datetime = DATEADD(Day, 6, @BegDate);
  DECLARE @Week2Begin datetime = DATEADD(Day, 7, @BegDate);
  </code></pre>
  
  <p>...is now the (sub)question.</p>
  
  <h2>UPDATE 2</h2>
  
  <p>It works this way:</p>
  
  <pre><code>DECLARE 
  @Week1End datetime = DATEADD(Day, 6, @BegDate),
  @Week2Begin datetime = DATEADD(Day, 7, @BegDate);
  </code></pre>