This might work for a few more cases but no guarantee for all.

WITH SampleData (sourcedata) AS

(

SELECT '12 Jan 2013 Test.docx'

UNION ALL SELECT '01 February 2001 File.pptx'

UNION ALL SELECT 'Meeting 04 Feb 2012.xls'

UNION ALL SELECT '09 September 2011.txt'

UNION ALL SELECT '30 Jan 13.doc'

UNION ALL SELECT '3.0 Meeting Minutes 16 January 2013.pdf'

UNION ALL SELECT '0.0 ICT Meeting (12 Jan 2013).doc'

UNION ALL SELECT 'ICTAgenda 15January 2013 V1.pptx'

UNION ALL SELECT 'ICTAgenda 15 January 2013 V2.pptx'

UNION ALL SELECT '20130616.xlsx'

UNION ALL SELECT '201307.xlsx'

UNION ALL SELECT 'HSE.201306.xlsx'

UNION ALL SELECT 'HSE.201307.xlsx'

UNION ALL SELECT '2013 ICT Calendar.xls'

UNION ALL SELECT 'Inv.10017355.txt'

),

SplitFNs AS

(

SELECT sourcedata, b.*

,rn=CASE WHEN LEFT(LTRIM(Item), 3) IN

('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec')

THEN ItemNumber END

FROM SampleData a

CROSS APPLY dbo.PatternSplitCM(sourcedata, '[0-9]') b

),

DateRecs AS

(

SELECT a.sourcedata, b.ItemNumber, b.Item

FROM SplitFNs a

JOIN SplitFNs b

ON b.ItemNumber BETWEEN a.rn - 1 AND a.rn + 1 AND a.sourcedata = b.sourcedata

)

SELECT sourcedata

,sourcedata2=CASE WHEN Flag = 0 THEN sourcedata

ELSE STUFF(sourcedata, CHARINDEX(FileDate, sourcedata), LEN(FileDate),CAST(FileDate AS DATE)) END

FROM SampleData a

CROSS APPLY

(

SELECT Item + ''

FROM DateRecs b

WHERE a.sourcedata = b.sourcedata

ORDER BY ItemNumber

FOR XML PATH('')

) b(FileDate)

CROSS APPLY

(

SELECT CASE WHEN LEN(FileDate) - LEN(REPLACE(FileDate, '-', '')) = 2 THEN 1 ELSE 0 END

) c(Flag);