The problem sounds more like that you're storing your datetime in your table as a varchar, rather than a datetime. Is my guess correct?

Also "gives you an error";...

Personally, I feel this might be a bit more concise:
DECLARE @FilePath VARCHAR(500) = '\\FileServer\\FileShare\\Directory\Sub Directory\My File Name.pdf';

SELECT @FilePath AS FilePath,
       RIGHT(@FilePath,...

Unless you have some logic, if you have an Excel document where there are blank lines before the end of the document, then all of these lines will still be...

It all depends what's bigger, the budget to pay a team to rebuild your entire reporting suite or pay for a new solution to allow you to deploy straight to...

SSIS does no understand named months (or at least I'm pretty sure it doesn't, as I know I have conversion expressions to deal with it in some my my packages)....

there's certainly nothing wrong with staging tables when doing ETL. Although it's sometimes "nice" to do every transformation in one hit, it's often a lot to ask to do something...

When you say "trying to convert", what is it you're attempting to do to convert the number as well?

Grr... Some of the schedule tasks failed this morning... Took me while to then notice someone has the repository folder... I have an idea who it is (as it could...

If you can't save the files to a file share first, then you're going to find this quite difficult.

What is the "real" reason your storing files, even temporarily,...

When you say it refuses to see it, do you mean it doesn't show up in the drop down? Considering you said it doesn't appear in SSMS in the networked...

CREATE TABLE #Supply
  (ITMREF_0 int,
  IPTDAT_0 date,
  BPRNUM_0 int,
  Price int);
GO

INSERT INTO #Supply
VALUES
  (1, '20161002', 1,...