SQL - How to declare date range in a temp table?

Question

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:

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 > @endDate)
    AND a.date is not null
    AND ae.excuse is not null
    AND sc.name = 'Elm Street School'

BUT - when I try to make a temp table with it as below, I get errors:

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 > @endDate)
    AND a.date is not null
    AND ae.excuse is not null
    AND sc.name = 'Elm Street School') x

Is there a 'correct' way to make this happen? This is the error result I get:

>[Error] Script lines: 9-39 -------------------------
 Incorrect syntax near the keyword 'Declare'.
 Msg: 156, Level: 15, State: 1, Procedure: , Line: 4 

>[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] 

Thank You!

This is the code that gives me an error of invalid column names:

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 > eDate)
    AND a.date is not null
    AND ae.excuse is not null
    AND sc.name = 'Elm Street School') x

Error:

>[Error] Script lines: 2-31 -------------------------
 Invalid column name 'sDate'.
 Msg: 207, Level: 16, State: 1, Procedure: , Line: 24 

>[Error] Script lines: 2-31 -------------------------
 Invalid column name 'eDate'.
 Msg: 207, Level: 16, State: 1, Procedure: , Line: 24 

>[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] 

Answer 1

I would suggest putting the values in a subquery and then using those values. In MySQL:

CREATE TEMPORARY TABLE Atendance as
    SELECT . . .
    FROM (SELECT date('2015-09-01') as startDate, date('2015-09-30') as endDate 
         ) params CROSS JOIN
         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 params.startDate and params.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 > params.endDate)