SQL Server 2008.
I have a file name that contains date information that I need to load into a date field. To get the date information I'm using a substring. I am not able to convert the substring to a date, please help.
File Name: XXXX_XXXXRN_FLAT10_XXX_20131230_0731.DAT
substring: SUBSTRING(FILE_NM,24,4)+'-'+SUBSTRING(FILE_NM,28,2)+'-'+SUBSTRING(FILE_NM,30,2)+' 00:00:00.000'
Returns: 2013-12-30 00:00:00.000
I am not able to update the 2013-12-30 00:00:00.000 to a date field. I have tried CAST and CONVERT on the substring with no luck.