0

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 < 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.

3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.