This should handle pretty much anything you can throw at it.

-- DROP TABLE #FileName

--===== Create a test table with some normal and "odd" file/path names.

SELECT FileName = 'C:/Location/Folder/Subfolder/filename20130101.xxx'

INTO #FileName UNION ALL

SELECT FileName = 'C:/Location/Folder/Subfolder/filename20130102' UNION ALL

SELECT FileName = 'filename20130103' UNION ALL

SELECT FileName = 'filename20130104.xxx' UNION ALL

SELECT FileName = '20130105.xxx' UNION ALL

SELECT FileName = '20130106' UNION ALL

SELECT FileName = '20130107def' UNION ALL

SELECT FileName = 'abc20130108def' UNION ALL

SELECT FileName = 'abc20130109def.xxx' UNION ALL

SELECT FileName = '123abc20130110def.xxx' UNION ALL

SELECT FileName = 'abcdef.xxx' --will not show up because has no numbers

;

--===== Get just the last set of numbers.

SELECT fn.FileName, DateOnly = LEFT(FromTheNumbers,ISNULL(NULLIF(PATINDEX('%[0-9][^0-9]%',FromTheNumbers),0),8000))

FROM #FileName fn

CROSS APPLY (SELECT RIGHT(FileName,ISNULL(NULLIF(PATINDEX('%[0-9][^0-9]%',REVERSE(FileName)),0),8000))) ca (FromTheNumbers)

WHERE fn.FileName LIKE '%[0-9]%'

;

Results:

FileName DateOnly

------------------------------------------------- --------

C:/Location/Folder/Subfolder/filename20130101.xxx 20130101

C:/Location/Folder/Subfolder/filename20130102 20130102

filename20130103 20130103

filename20130104.xxx 20130104

20130105.xxx 20130105

20130106 20130106

20130107def 20130107

abc20130108def 20130108

abc20130109def.xxx 20130109

123abc20130109def.xxx 20130110

(10 row(s) affected)