title: SQL - How to declare date range in a temp table?
body: <p>I'd like to assign a value to dates I'm using so I don't have to go through the code to make the changes each time I want to look at a different month. When I submit the following code, it works fine:</p>
  
  <pre><code>Declare @startDate datetime = '9/1/2015'
  Declare @endDate datetime = '9/30/2015'
  SELECT a.attendanceID
  , p.stateID
  , p.personID
  , i.lastName
  , i.firstName
  , en.grade
  , a.date
  , ae.status
  , ae.excuse
  , ae.description
  , prd.name
  FROM enrollment en
  LEFT JOIN enrollmentME em on en.enrollmentID = em.enrollmentID
  LEFT JOIN person p on p.personID = en.personID
  LEFT JOIN [Identity] i on p.currentIdentityID = i.identityID and p.personID = i.personID
  LEFT JOIN Calendar c on en.calendarID = c.calendarID
  LEFT JOIN SchoolYear sy ON sy.endYear = c.endYear AND sy.active = 1
  LEFT JOIN School sc ON sc.schoolID = c.schoolID
  LEFT JOIN Attendance a on a.personID = p.personID and a.date between @startDate and @endDate
  LEFT JOIN AttendanceExcuse ae on a.excuseID = ae.excuseID AND a.calendarID = c.calendarID
  LEFT JOIN period prd on prd.periodID = a.periodID
  WHERE (en.endDate IS NULL or en.endDate &gt; @endDate)
      AND a.date is not null
      AND ae.excuse is not null
      AND sc.name = 'Elm Street School'
  </code></pre>
  
  <p>BUT - when I try to make a temp table with it as below, I get errors:</p>
  
  <pre><code>SELECT *
  INTO #Attendance
  FROM
  (Declare @startDate datetime = '9/1/2015'
  Declare @endDate datetime = '9/30/2015'
  SELECT a.attendanceID
  , p.stateID
  , p.personID
  , i.lastName
  , i.firstName
  , en.grade
  , a.date
  , ae.status
  , ae.excuse
  , ae.description
  , prd.name
  FROM enrollment en
  LEFT JOIN enrollmentME em on en.enrollmentID = em.enrollmentID
  LEFT JOIN person p on p.personID = en.personID
  LEFT JOIN [Identity] i on p.currentIdentityID = i.identityID and p.personID = i.personID
  LEFT JOIN Calendar c on en.calendarID = c.calendarID
  LEFT JOIN SchoolYear sy ON sy.endYear = c.endYear AND sy.active = 1
  LEFT JOIN School sc ON sc.schoolID = c.schoolID
  LEFT JOIN Attendance a on a.personID = p.personID and a.date between @startDate and @endDate
  LEFT JOIN AttendanceExcuse ae on a.excuseID = ae.excuseID AND a.calendarID = c.calendarID
  LEFT JOIN period prd on prd.periodID = a.periodID
  WHERE (en.endDate IS NULL or en.endDate &gt; @endDate)
      AND a.date is not null
      AND ae.excuse is not null
      AND sc.name = 'Elm Street School') x
  </code></pre>
  
  <p>Is there a 'correct' way to make this happen?  This is the error result I get:</p>
  
  <pre><code>&gt;[Error] Script lines: 9-39 -------------------------
   Incorrect syntax near the keyword 'Declare'.
   Msg: 156, Level: 15, State: 1, Procedure: , Line: 4 
  
  &gt;[Error] Script lines: 9-39 -------------------------
   Incorrect syntax near ')'.
   Msg: 102, Level: 15, State: 1, Procedure: , Line: 30 
  
   [Executed: 12/31/2015 9:45:40 AM] [Execution: 47ms] 
  </code></pre>
  
  <p>Thank You!</p>
  
  <p>This is the code that gives me an error of invalid column names:</p>
  
  <pre><code>SELECT *
  INTO #Attendance
  FROM
  (SELECT cast('9/1/2015' as date) as sDate
  ,cast('9/30/2015' as date) as eDate 
  ,a.attendanceID
  , p.stateID
  , p.personID
  , i.lastName
  , i.firstName
  , en.grade
  , a.date
  , ae.status
  , ae.excuse
  , ae.description
  , prd.name
  FROM enrollment en
  LEFT JOIN enrollmentME em on en.enrollmentID = em.enrollmentID
  LEFT JOIN person p on p.personID = en.personID
  LEFT JOIN [Identity] i on p.currentIdentityID = i.identityID and p.personID = i.personID
  LEFT JOIN Calendar c on en.calendarID = c.calendarID
  LEFT JOIN SchoolYear sy ON sy.endYear = c.endYear AND sy.active = 1
  LEFT JOIN School sc ON sc.schoolID = c.schoolID
  LEFT JOIN Attendance a on a.personID = p.personID and a.date between sDate and eDate
  LEFT JOIN AttendanceExcuse ae on a.excuseID = ae.excuseID AND a.calendarID = c.calendarID
  LEFT JOIN period prd on prd.periodID = a.periodID
  WHERE (en.endDate IS NULL or en.endDate &gt; eDate)
      AND a.date is not null
      AND ae.excuse is not null
      AND sc.name = 'Elm Street School') x
  </code></pre>
  
  <p>Error:</p>
  
  <pre><code>&gt;[Error] Script lines: 2-31 -------------------------
   Invalid column name 'sDate'.
   Msg: 207, Level: 16, State: 1, Procedure: , Line: 24 
  
  &gt;[Error] Script lines: 2-31 -------------------------
   Invalid column name 'eDate'.
   Msg: 207, Level: 16, State: 1, Procedure: , Line: 24 
  
  &gt;[Error] Script lines: 2-31 -------------------------
   Invalid column name 'eDate'.
   Msg: 207, Level: 16, State: 1, Procedure: , Line: 27 
  
   [Executed: 12/31/2015 10:58:07 AM] [Execution: 62ms] 
  </code></pre>