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