Converting Varchar value

  • I receive an error when I attempt to run this code.

    Select VwPlanYears.PlanNumber, VwPlanYears.CompanyName, VwPlanYears.ValStatus, VwPlanYears.Analyst, VwPlanYears.PlanConsultant,VwAdmin.Manager, VwPlanYears.CensusReceived, VwPlanYears.CensusAudited, VwPlanYears.AnalystResolved, VwPlanYears.ValAssignedByAnalyst, VwPlanYears.ConsultantUnresolved, VwPlanYears.ValRevUnresolvedReason, VwPlanYears.ValRevReason, VwPlanYears.ConsultantResolved, VwPlanYears.ValDue, VwPlanYears.ValMailed, VwPlanYears.PYEAssets, VwPlanYears.DaysFromAnalystRslvToConsUnrslv, VwPlanYears.DaysToCompleteVal, VwPlanYears.Form5500,

    VwPlanYears.Form5500Type, VwPlanYears.Form5500ExtFiled, VwPlanYears.Form5500ToClient, VwPlanYears.Allocation, VwPlanYears.PlanStatus, VwPlanYears.ADPTestDate, VwPlanYears.ADPTestStatus, Case When Valstatus = 'All Data' Then DateDiff(d,[ConsultantResolved],GetDate())+DateDiff(d,[AnalystResolved],[ConsultantUnresolved]) Else 'N/A' END AS [Aging Days]

    FROM vwAdmin LEFT JOIN vwPlanYears ON vwAdmin.PlanID = vwPlanYears.PlanID

    WHERE (vwPlanYears.PlanNumber Like '63%') And (VwPlanYears.PlanConsultant Is not Null)And (VwPlanyears.Allocation Between @Startdate and @Enddate) And (VwPlanYears.PlanStatus <> 'done') And (VwAdmin.SubPPA is Null)

    ORDER BY vwPlanYears.CensusReceived DESC;

    I receive conversion failed when converting the varchar value 'n/a' to data type int. can someone correct this or give me direction.

  • This is the code that is causing the error:

    Case When Valstatus = 'All Data'

    Then DateDiff(d, [ConsultantResolved], GetDate()) + DateDiff(d, [AnalystResolved], [ConsultantUnresolved])

    Else 'N/A'

    END AS [Aging Days]

    I'd do this:

    Case When Valstatus = 'All Data'

    Then CONVERT(VARCHAR(10), DateDiff(d, [ConsultantResolved], GetDate()) + DateDiff(d, [AnalystResolved], [ConsultantUnresolved]))

    Else 'N/A'

    END AS [Aging Days]

  • The problem is within in your case statement:

    The DATEDIFF function returns an integer, so the resulting value of the CASE statement is always an integer as well. So your ELSE condition violates the data type chosen by SQL Server. INT has a higher precedence than VARCHAR (for details please see BOL).

    The error can be resolved by converting the result of your DATEDIFF to e.g. VARCHAR(5) (allowing to store the datediff value for an equivalent of 30+ years).



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Jack, that worked like a charm. I will remeber to cast or convert next time. Thanks for your kind attention.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply