title: Why is there an Overflow in this DateTime?
body: <p>I am trying to create a dynamic calendar where I can ignore weekends and skip ahead to 45 days from now</p>
  
  <pre><code>Declare @StartDate Date = '12/01/2015'
  
  ;With NumberList AS
  (
          Select *
          From 
          (
              Select Rank() Over(Order By S1.Id, S2.Id) Number
              From master.dbo.SysObjects S1, master.dbo.SysObjects S2
          ) XX
          Where Number &lt; 1000
  )
  ,FullCalendar as
  (
      select Cast (dateadd(day, number,  DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)) as Date) CalendarDate
      from Numberlist
  )
  ,CalendarWithNumbers As 
  (
      Select CalendarDate, Row_Number() Over (Order By CalendarDate) DayNumber
      From FullCalendar
      Where ((DATEPART(dw, CalendarDate) + @@DATEFIRST) % 7) NOT IN (0, 1)
  )
  ,StartingDate as
  (
      Select *
      From CalendarWithNumbers
      Where 1=1
          And CalendarDate = @StartDate
  )
  Select @StartDate StartDate, CalendarDate NDaysFromNow, DayNumber
  From CalendarWithNumbers 
  Where DayNumber = (Select DayNumber + 45 from StartingDate)
  </code></pre>
  
  <p>However, I get an an Overflow Error </p>
  
  <pre><code>StartDate  NDaysFromNow DayNumber
  ---------- ------------ --------------------
  Msg 517, Level 16, State 1, Line 3
  Adding a value to a 'datetime' column caused an overflow.
  </code></pre>
  
  <p>However, I am only choosing the first 1000 numbers in my first CTE, and my starting date is the first day of the year.  <code>1/1/2015</code> + 1000 days only goes up to <code>9/26/2017</code>.</p>
  
  <p>Why I am getting an Overflow Error?</p>
  
  <hr>
  
  <h2>EDIT - Comments added to explain code</h2>
  
  <p>I added a comments to the code to make it easier to understand.</p>
  
  <pre><code>Declare @StartDate Date = '12/01/2015'
  
  ;With NumberList AS
  (
          /* Get a list of 1000 Numbers*/
          Select *
          From 
          (
              /* 
                  Get a sequence of Numbers (I get 2071*2071 = 4289041) 
                  I may need to go up to 50 years in the future so I have to do a cartesian product (same as Cross Join)
                  However, this is where it fails
              */
  
              Select Rank() Over(Order By S1.Id, S2.Id) Number
              From master.dbo.SysObjects S1, master.dbo.SysObjects S2
  
              /*
                  comment out the Select ABOVE 
                  Uncomment the Select BELOW
                  It works!!
                  Probably because now the sequencing only goes up to 2071
              */
              --Select Rank() Over(Order By S1.Id) Number
              --From master.dbo.SysObjects S1
  
          ) XX
          Where Number &lt; 1000
  )
  --Select * From NumberList
  --UnComment just the line above, and comment everything below to see the result just up to there
  
  ,FullCalendar as
  (
      /* Take the first day of the year, and add 1 to 1000 days to it
      It starts from 2015-01-02 (bug needs to be fixed so it starts from 2015-01-01)
      It ends at 2017-09-26
      */
      select Cast (dateadd(day, number,  DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)) as Date) CalendarDate
      from Numberlist
  )
  --Select * From FullCalendar
  --UnComment just the line above, and comment everything below to see the result just up to there
  
  ,CalendarWithNumbers As 
  (
      /* 
      WHERE CLAUSE exclude the weekend days
      ROW_NUMBER now number each day sequentially and save it in the DayNumber column
      */
      Select CalendarDate, Row_Number() Over (Order By CalendarDate) DayNumber
      From FullCalendar
      Where ((DATEPART(dw, CalendarDate) + @@DATEFIRST) % 7) NOT IN (0, 1)
  )
  --Select * From CalendarWithNumbers
  --UnComment just the line above, and comment everything below to see the result just up to there
  
  ,StartingDate as
  (
      /* Get the DayNumber (from the query above) for the starting day*/
      Select *
      From CalendarWithNumbers
      Where 1=1
          And CalendarDate = @StartDate
  )
  --Select * From StartingDate
  --UnComment just the line above, and comment everything below to see the result just up to there
  
  /* 
      Now calculate 45 days from the daynumber in starting date
      that gives you the value for NDays from StartDate
  */
  Select 
      SD.CalendarDate,
      Sd.DayNumber,
      CWN.CalendarDate NDaysFromNow, 
      CWN.DayNumber
  From CalendarWithNumbers CWN
  Inner Join StartingDate SD
      On CWN.DayNumber = SD.DayNumber + 345 
  </code></pre>