since you have everything in a staging table, shouldn't you just convert the columns explicitly?
-- Substring input patient
set @SQL = N'SELECT
CONVERT(int,SUBSTRING(BulkColumn, 1, 4)) AS patid
,SUBSTRING(BulkColumn, 5, 1) AS patflag
,CONVERT(datetime,SUBSTRING(BulkColumn, 6, 8)) AS yob
,SUBSTRING(BulkColumn, 14, 6) AS famnum
,SUBSTRING(BulkColumn, 20, 1) AS sex
,CONVERT(datetime,SUBSTRING(BulkColumn, 21, 8)) AS regdate
,SUBSTRING(BulkColumn, 29, 2) AS regstat
,CONVERT(datetime,SUBSTRING(BulkColumn, 31, 8)) AS xferdate
,SUBSTRING(BulkColumn, 39, 2) AS regrea
,CONVERT(datetime,SUBSTRING(BulkColumn, 41, 8)) AS deathdate
,SUBSTRING(BulkColumn, 49, 1) AS deathinfo
,SUBSTRING(BulkColumn, 50, 1) AS accept
,SUBSTRING(BulkColumn, 51, 1) AS institute
,SUBSTRING(BulkColumn, 52, 2) AS marital
,SUBSTRING(BulkColumn, 54, 1) AS dispensing
,SUBSTRING(BulkColumn, 55, 2) AS prscexempt
,CONVERT(datetime,SUBSTRING(BulkColumn, 57, 8)) AS sysdate
into ' + @tblname + ' FROM #stage'
exec TestDB..sp_executesql @SQL
Lowell