Have you also considered FileTables for this?

Thanks, Jacob, for the correction.

The fact that the result returned by

SELECT CAST('YYYY-MM-DD' AS date);

is language dependent, and therefore non-deterministic, is enough reason for me to use the 'YYYYMMDD' format, for which the above expression...

If it were me, I think I'd resort to a script component, as there are too many nested IFs for my liking.

However, things could be made much easier on the...

you can install OpenXML SDK and use that to create Excel Files without the need for Office to be installed.

But yes it does require coding in C# to...

As far as I know (and I will happily be proved wrong), programmatic formatting of Excel sheets requires the installation of the Excel application. Without that, you do not have...

For someone with...

This is on 2017.

Can someone else please test on 2019? Or maybe explain why it would not work there?

I started out down this path, but found that carving out all of the individual components resulted in an even longer...

Are you saying that my code 'does not work'? If so, please provide...

I'm sure that this could be simplified with a bit more thought, but this works for the example provided:

DECLARE @Text NVARCHAR(250) = N'CTranTyp-ViewId_2020.03.24_13h05min10s.xls';

SELECT
    ...

OK, so my code should work, with some refinements to handle the zeros.

DECLARE @DateStr NVARCHAR(50) = N'12172019';
SELECT @DateStr,
    CAST(CONCAT(RIGHT(@DateStr, 4), LEFT(@DateStr, 2), SUBSTRING(@DateStr, 3, 2)) AS DATE);

• This reply was modified 5 years, 10 months ago by Phil Parkin.

"Not working" is not helpful. What is the error message?

NVARCHAR needs a length.

What is the data type of [due date]? If it's a date, why not just

CAST([due date]...

"Not working" is not helpful. What is the error message?

NVARCHAR needs a length.

What is the data type of [due date]? If it's a date, why not just

CAST([due date] as DATE)

?