some of your dates are not dates...probably blank strings for death date(a reasonable assumption would be not all patients die or get transferred)
something like this:
...
CASE
WHEN ISDATE(SUBSTRING(BulkColumn, 41, 8)) =1
THEN CONVERT(datetime,SUBSTRING(BulkColumn, 41, 8)) ELSE NULL
END AS deathdate,
...
you'll need to do that for any of the columns that might be empty strings i think.
Lowell