0

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
3

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.

0

The issue was not with

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' 

it was to do with my join at the very end

INNER JOIN CTE5 CT5
ON ct5.New_Date_Last_Data_Changed = c1.Year_Last_Accesed

I had to cast it that to this :

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 = CAST(c1.Year_Last_Accesed AS varchar (255))
  • So you're meaning to join on a string like 'Has Been changed recently'? This might have removed one error but I don’t think the query will work the way you expect (e.g. now you get no results... which may or may not be better than an error). – Aaron Bertrand Apr 11 '18 at 14:01
  • @AaronBertrand Yes you are right ,it did remove the error but I got no results back ,I need figure out how I could join the two CTEs ,the problem is that if I add an extra field in the first CTE 1 ,the calculation will be ruined because I have to group with another field . – Lucy Apr 12 '18 at 8:39
  • 1
    This question was about solving the error. I suggest creating a new question but instead of just posting your query, post sample data and desired results. I’m sure there’s a less cumbersome way to get your expected output. – Aaron Bertrand Apr 12 '18 at 10:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.