Problem with cast convert and view all in TSQL

Answer 1

Nothing you do in the query with the formatting adds/removes leading zeros, so they must not be in the table. Leading zeros can only exist in a string based data type column, and then only if they are put there on purpose.

My best guess based on all given information is that Table1.CC_RENAL_SUPPORT_DAYS_1 is a char or varchar and has leading zeros in the column and that vw_FormatTable1.[CC_RENAL_SUPPORT_DAYS_1] is an int (or a char/varchar with no leading zeros).

if you need leading zeros in the second query, use this:

select 
    [CC_RENAL_SUPPORT_DAYS_1]
        ,RIGHT(ISNULL(REPLICATE('0',3)+CONVERT(VARCHAR, [CC_RENAL_SUPPORT_DAYS_1]),''),3) AS [CC_RENAL_SUPPORT_DAYS_SPC]
    from vw_FormatTable1 
    group by [CC_RENAL_SUPPORT_DAYS_1]