Extracting date from file group name and converting to date fails when filtering
The problem is that for some values the resulting trim isn't a date so it can't be converted to one.

This is your current REPLACE function:

DECLARE @FileGroupName VARCHAR(100) = 'Event_Header_Daily_2018_08_09_FG'
SELECT REPLACE(REPLACE(REPLACE(@FileGroupName, 'Event_Header_Daily_', ''), '_FG',''), '_', '-')
-- Result: 2018-08-09


This is the value in which it won't work (you filtered one but there might be others similar to this one):

DECLARE @FileGroupName VARCHAR(100) = 'Event_Header_Daily_LeftNull_FG'
SELECT REPLACE(REPLACE(REPLACE(@FileGroupName, 'Event_Header_Daily_', ''), '_FG',''), '_', '-')
-- Result: LeftNull

SELECT CAST('LeftNull' AS DATE)
-- Conversion failed when converting date and/or time from character string.


TRY_PARSE() function won't throw an error whenever the cast can't be done, it will return NULL.

SELECT TRY_CAST('LeftNull' AS DATE)
-- Result: NULL


You should switch your explicit CAST with a TRY_PARSE:

SELECT *
FROM
(

    SELECT 
        groupname,
        TRY_PARSE(REPLACE(REPLACE(REPLACE(groupname, 'Event_Header_Daily_', ''), '_FG',''), '_', '-') AS DATE) AS dt
    FROM sys.sysfilegroups
    WHERE groupname LIKE 'Event_Header_Daily_%'
        AND groupname NOT IN ('Event_Header_Daily_LeftNull_FG')
) a
WHERE dt &lt; GETDATE()


If you want to know which values are giving casting problems, you can check where the TRY_PARSE IS NULL.

SELECT 
    groupname,
    REPLACE(REPLACE(REPLACE(groupname, 'Event_Header_Daily_', ''), '_FG',''), '_', '-') AS dt
FROM sys.sysfilegroups
WHERE groupname LIKE 'Event_Header_Daily_%'
    AND groupname NOT IN ('Event_Header_Daily_LeftNull_FG')
    AND TRY_PARSE(REPLACE(REPLACE(REPLACE(groupname, 'Event_Header_Daily_', ''), '_FG',''), '_', '-') AS DATE) IS NULL