Try this:

REPLACE(accountx, '-', '.')

Just delete the rows.  If it's not that many rows, it's more work than it's worth to try to TRUNCATE.

I suspect (hope) the temp table is clustered first...

For this particular need, you just need to touch up your original script to bit to avoid unnecessary errors:

CREATE TABLE ##temp_fbl
(
DBName nvarchar(200),

CAST is more flexible than specifying a specific format.

Thus, I'd suggest TRY_CAST ... AS date to perhaps allow more data to be automatically converted to a date.

I suggest starting with your actual solution, not listing things you would not do first.  You can offer to explain why you wouldn't use other possible solutions if they're interested in...

;WITH cte_test_data AS (
    SELECT * FROM ( VALUES
        ('00000S123', 'US', 'ITALY'),
        ('00000BH01', 'UK', ''),
   ...

I agree with the other critiques.  As to "the guy", he's 100% right, and he should already run, not walk, to another contractor, without delay!

1NF is trivial and...

Never use ISNULL() in a JOIN (or WHERE) is a good rule.

To avoid issues with NULL, perhaps you could use empty string as no suborder rather than NULL?

I suggest using MINUTE rather than HOUR as the difference between local and UTC.  Yes, some places actually do offset by not-an-even-hour, so, to be safe, use minutes.