-- use NULLIF to convert 0 to NULL
-- conversion code 112 matches the input style
-- Char(8) matches the input length
SELECT
aprvdt,
ApprovalDate = CONVERT(DATETIME2,CAST(NULLIF(aprvdt,0) AS CHAR(8)),112) -- 112 matches the input
FROM (
SELECT aprvdt = CAST(19970811 AS DECIMAL(18,0)) UNION ALL
SELECT 19970811 UNION ALL
SELECT 19970811 UNION ALL
SELECT 19970811 UNION ALL
SELECT 19970811 UNION ALL
SELECT 19970812 UNION ALL
SELECT 19970812 UNION ALL
SELECT 19970813 UNION ALL
SELECT 19970812 UNION ALL
SELECT 19970812 UNION ALL
SELECT 19970812 UNION ALL
SELECT 19970821 UNION ALL
SELECT 0
) [sample]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden