Well spotted! The SUBSTRING error is occurring because I'm willing to bet that the filepath your using has a space in it somewhere - xp_cmdshell doesn't support spaces (as far as I'm aware) so you have to use the short filename instead. This is easy enough to find by using the "dir /x" command in a DOS prompt. For example, say I have a path of:
The short filename would be:
I'll have to have a play at getting the file date into another column. If you remove the /b parameter (this is the "bare format" parameter) from the dir command you'll get it (along with a lot of other stuff you won't want!), but the CTE will error.