June 2, 2009 at 12:43 pm
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.
June 2, 2009 at 12:55 pm
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]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 2, 2009 at 12:57 pm
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).
June 3, 2009 at 6:28 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy