Explicit conversion of bigint to date

I'm fixing an existing code but got stuck with it because SQL server keeps showing an error related to converting string to date and then bigint to date. so not sure which technique to use:

below is my code:

SELECT
c.WCFromDate
,c.WCToDate
,CONVERT(date, CAST(
                    CASE
                        WHEN c.WCFromDate = 0  THEN 19000101
                        WHEN SUBSTRING(CAST(c.WCFromDate as varchar(10)), 5, 2) = '00' AND RIGHT(c.WCFromDate,2) = '00'
                            THEN LEFT(c.WCFromDate, 4) * 10000 + 0101
                        WHEN SUBSTRING(CAST(c.WCFromDate as varchar(10)), 5, 2) = '02' AND RIGHT(c.WCFromDate,2) > '29'
                            THEN LEFT(c.WCFromDate, 4) * 10000 + 0228
                        WHEN SUBSTRING(CAST(c.WCFromDate as varchar(10)), 5, 2) < '13' AND RIGHT(c.WCFromDate,2) > '31'
                            THEN LEFT(c.WCFromDate, 4) * 10000 + 0101
                        WHEN SUBSTRING(CAST(c.WCFromDate as varchar(10)), 5, 2) > '12'
                            THEN LEFT(c.WCFromDate, 4) * 10000 + 0101
                        WHEN RIGHT(c.WCFromDate,2) = '00'
                            THEN LEFT(c.WCFromDate, 4) * 10000 + 0101
                        ELSE c.WCFromDate
                    END AS varchar(10))
        ) AS [Worker Compensation From Date]
,CONVERT(date, CAST(
                    CASE
                        WHEN c.WCToDate = 0 THEN 19000101
                        WHEN SUBSTRING(CAST(c.WCToDate as varchar(10)), 5, 2) = '00' AND RIGHT(c.WCToDate,2) = '00'
                            THEN LEFT(c.WCToDate, 4) * 10000 + 0101
                        WHEN SUBSTRING(CAST(c.WCToDate as varchar(10)), 5, 2) = '02' AND RIGHT(c.WCToDate,2) > '29'
                            THEN LEFT(c.WCToDate, 4) * 10000 + 0228
                        WHEN SUBSTRING(CAST(c.WCToDate as varchar(10)), 5, 2) < '13' AND RIGHT(c.WCToDate,2) > '31'
                            THEN LEFT(c.WCToDate, 4) * 10000 + 0101
                        WHEN SUBSTRING(CAST(c.WCToDate as varchar(10)), 5, 2) > '12'
                            THEN LEFT(c.WCToDate, 4) * 10000 + 0101
                        WHEN RIGHT(c.WCFromDate,2) = '00'
                            THEN LEFT(c.WCFromDate, 4) * 10000 + 0101
                        ELSE c.WCToDate
                    END AS varchar(10))
        ) AS [Worker Compensation To Date]
        FROM  ls3dwh.Claims c

To be more specific, all case when statements are working fine but the ELSE part is causing this error. I used ELSE NULL to find out more and noticed that all ELSE values are causing this error.

The column values are pretty simple, below screenshot can help:

all 4 columns

And here is some sample data from the first two columns. I used a select distinct:

   WCFromDate   WCToDate
   0        0
   19456601 20030000
   19580800 20300500
   19670000 0
   19840529 19840603
   19850314 19851115
   19851121 19910110
   19860217 19861112

The error I get is this:

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

Solution

Have you considered using IsDate(MyColumnValue) to check for valid dates, rather than parsing out over several case statements to check for date validity? That may make the process a little cleaner and easier to understand and debug, and may help you get the output you need. I just quick-checked and it works "as is" with values formatted like those you've shared.