Extracting date from file group name and converting to date fails when filtering In sys.sysfielgroups I have a list of filegroups with dates in them, and I'm trying to extract these dates.  Here's an example list of the file groups:


  Event_Header_Daily_LeftNull_FG
  
  Event_Header_Daily_2018_08_07_FG
  
  Event_Header_Daily_2018_08_08_FG
  
  Event_Header_Daily_2018_08_09_FG
  
  Event_Header_Daily_2018_08_10_FG  2018-08-10


This is the query that I'm trying to run.  As you can see, I'm trying to get any file group older than today's date:

SELECT *
FROM
(

    SELECT 
        groupname,
        CAST(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 i run the subselect without filtering the results, the query runs just fine.  However, when running the whole query I get the 7th, 8th, and 9th and the query errors out, stating:


  Conversion failed when converting date and/or time from character
  string.


As far as I can tell this should just work, but it doesn't and I'm at a loss as to why.
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