• I finally got it. This works the way I need it to:

    CASE

    WHEN CAS.TERM_YYYYMMDD != '' Then convert(varchar,CONVERT(DATETIME,CAS.TERM_YYYYMMDD),101)

    WHEN IND.TERM_YYYYMMDD != '' Then convert(varchar,CONVERT(DATETIME,IND.TERM_YYYYMMDD),101)

    WHEN CLM.TERM_YYYYMMDD_02 != '' Then convert(varchar,CONVERT(DATETIME,CLM.TERM_YYYYMMDD_02),101)

    WHEN convert(varchar,CONVERT(DATETIME,[1_tbl_PEOPLE W/ DENTAL].TERM_YYYYMMDD),101) != '1/1/1900' Then convert(varchar,CONVERT(DATETIME,[1_tbl_PEOPLE W/ DENTAL].TERM_YYYYMMDD),101)

    ELSE convert(varchar,CONVERT(DATETIME,CAS.TERM_YYYYMMDD),101)

    end as TERM_YYYYMMDD