Without being able to see the complete query, it's impossible to come up with the best solution. The simplest solution is to add the criteria as a HAVING clause:

HAVING `C4630` <> 'NOT IN RANGE'

I'm confused about your date format. If this is supposed to be MySQL queries, they should be in yyyy-mm-dd format. Arguably, they should be in this format even on SQL Servers that support ambiguous native date formats.

Why are they all RTRIM and CAST? Data should be stored in the correct format and sanitized on input, if this is not possible, sanitize the data regularly rather than for every query.

It would be wise to move your seasons into their own table rather than defining them at query time. Then it's a simple connection to seasons. I'm using a subquery here, but hopefully you get the idea:

SELECT
    /* other columns */
    COALESCE(seasons.season, FTBL_TEST_SCORES.test_admin_period) AS `C4630`
FROM all_the_other_tables
LEFT JOIN (

    SELECT 'Bay Area' AS region, 'FALL' AS season, '2021-08-07' AS start, '2021-09-08' AS end UNION ALL
    SELECT 'Bay Area'          , 'WINTER'        , '2021-11-27'         , '2021-12-15'        UNION ALL
    SELECT 'Bay Area'          , 'SPRING'        , '2022-03-04'         , '2022-03-22'        UNION ALL
        
    SELECT 'Central Valley'    , 'FALL'          , '2021-07-31'         , '2021-09-01'        UNION ALL
    SELECT 'Central Valley'    , 'WINTER'        , '2021-11-27'         , '2021-12-15'        UNION ALL
    SELECT 'Central Valley'    , 'SPRING'        , '2022-02-19'         , '2022-03-08'        UNION ALL
        
    SELECT 'Los Angeles'       , 'FALL'          , '2021-08-14'         , '2021-09-15'        UNION ALL
    SELECT 'Los Angeles'       , 'WINTER'        , '2021-11-27'         , '2021-12-15'        UNION ALL
    SELECT 'Los Angeles'       , 'SPRING'        , '2022-03-04'         , '2022-03-22'

) AS seasons
    ON dtbl_school_dates.local_school_year = '2021-2022'
    AND dtbl_schools_ext.region = seasons.region
    AND dtbl_school_dates.date_value BETWEEN seasons.start AND seasons.end
WHERE (
    (dtbl_school_dates.local_school_year = '2021-2022' AND seasons.season IS NOT NULL) OR
    dtbl_school_dates.local_school_year <> '2021-2022'
);

You can repeat the entire CASE statement in the WHERE clause as follows:

select
    case
        when x then y 
        when a then b 
        when c then d 
        else 'NOT IN RANGE'
    end as foo
from
    table t 
where 
    case
        when x then y 
        when a then b 
        when c then d 
        else 'NOT IN RANGE'
    end <> 'NOT IN RANGE'

Or you can use a subquery (or CTE) like this:

select * 
from (
    select
        case
            when x then y 
            when a then b 
            when c then d 
            else 'NOT IN RANGE'
        end as foo
    from
        table t 
) 
where foo <> 'NOT IN RANGE'