Sql-server – Conversion failed when converting the varchar value ‘Jan 1 1900 12:00AM’ to data type int

ctesql server

I'm getting the following error :

Conversion failed when converting the varchar value 'Jan  1 1900 12:00AM' to data type int.

I have tried casting the problematic field which is in my third CTE(CTE4)

WHEN max(Date_Last_Data_Changed )= '1900-01-01 00:00:00.000' THEN 'Has never been changed'

I have looked at different places

Conversion failed when converting the varchar value to data type int.

Conversion failed when converting the varchar value 'simple, ' to data type int

But still was not able to figure out this problem 

 ;with CTE1 AS (
    Select Year_Last_Accesed, 
    (Count(Year_Last_Accesed)* 100 / (select Count(*) From _Admin_Database_Objects_Analysis_Tbl)) as Percentage_Result
    From _Admin_Database_Objects_Analysis_Tbl
    Group by Year_Last_Accesed
    ),

     CTE2 AS(
    Select Year_Last_Accesed, 
    (Count(Year_Last_Accesed)* 100 / (select Count(*) From _Admin_Database_Objects_Analysis_Tbl)) as [10 Years older Tables]
    From _Admin_Database_Objects_Analysis_Tbl
    Where Date_Created > DATEADD(year,-10,GETDATE()) 
    Group by Year_Last_Accesed
    )


    ,
    CTE4 AS (

    SELECT Object_Name,
           MAX(Date_Last_Data_Changed) as [Last Time Data Changed]
           ,CASE 
                WHEN max(cast(Date_Last_Data_Changed as varchar(255))) >1900 THEN 'Has Been changed recently'
                WHEN max(Date_Last_Data_Changed )= '1900-01-01 00:00:00.000' THEN 'Has never been changed' 
                end as New_Date_Last_Data_Changed
           FROM _Admin_Database_Objects_Analysis_Tbl
           GROUP BY Object_Name

    ),

    CTE5 AS (
        SELECT 
        ct4.New_Date_Last_Data_Changed
        ,(Count(New_Date_Last_Data_Changed)* 100 / (select Count(*) From _Admin_Database_Objects_Analysis_Tbl)) as [Data Changed %]

        FROM _Admin_Database_Objects_Analysis_Tbl ct5
        Inner Join CTE4 ct4
        on ct4.Object_Name = ct5.Object_Name
        Group by ct4.New_Date_Last_Data_Changed
    )
    Select Percentage_Result,
            case 
            When c1.Year_Last_Accesed  = '1900' then 'Never Used'
            When c1.Year_Last_Accesed  = '2018' then '2018'
            end as Year_Last_Accesed
            ,[10 Years older Tables]


     FROM CTE1 c1
     INNER JOIN CTE2 c2 
     ON c1.Year_Last_Accesed=c2.Year_Last_Accesed
     INNER JOIN CTE5 CT5
     ON ct5.New_Date_Last_Data_Changed = c1.Year_Last_Accesed

Best Answer

You have:

max(cast(Date_Last_Data_Changed as varchar(255))) >1900

So you're converting to a string, and then comparing that string to an integer. I think you meant:

max(convert(char(8), Date_Last_Data_Changed, 112)) > '19000101'

Or just

max(Date_Last_Data_Changed) > '19000101'

Note that converting to varchar(255) without a style (e.g. 112) yields a string that will not sort the way you expect, since it is sorting as a string, and when you put month name first, it doesn't work. Dec < Jan, for example.

Related Question