I have recreated your table as @AcadmicDates and populated as such like below
DECLARE @AcademicDates TABLE (
AcademicTerm VARCHAR(6) NULL,
AcademicYear INT NULL,
StartDate DATE NULL,
EndDate DATE NULL
)
INSERT @AcademicDates
VALUES ('Fall', 2014, '10/12/2014', '01/22/2015')
, ('Fall', 2015, '09/13/2015', '01/05/2016')
, ('Spring', 2015, '02/15/2015', '05/28/2015')
Your portion of "extending the date" range really is a simple query:
SELECT AcademicYear, AcademicTerm
FROM @AcademicDates
WHERE '10/12/2014' BETWEEN StartDate AND EndDate
And could be used to populate a secondary table
DECLARE @DateToAcademicDate TABLE (
DateToCheck DATE NULL,
AcademicYear INT NULL,
AcademicTerm VARCHAR(6) NULL
)
DECLARE @RangeDate DATE = '10/12/2014'
WHILE (@RangeDate <= '01/05/2016') BEGIN
IF EXISTS(SELECT 1 FROM @AcademicDates WHERE @RangeDate BETWEEN StartDate AND EndDate) BEGIN
INSERT @DateToAcademicDate
SELECT @RangeDate, AcademicYear, AcademicTerm
FROM @AcademicDates
WHERE @RangeDate BETWEEN StartDate AND EndDate
END
SET @RangeDate = DATEADD(dd, 1, @RangeDate)
END
Which will return the following
DateToCheck AcademicYear AcademicTerm
----------- ------------ ------------
2014-10-12 2014 Fall
... 2014 Fall
2015-01-22 2014 Fall
2015-02-15 2015 Spring
... 2015 Spring
2015-05-28 2015 Spring
2015-09-13 2015 Fall
... 2015 Fall
2016-01-05 2015 Fall