Indeed you'll be CONVERTing your way to happiness. ;-)
However your individual field datatypes will become much more critical once you've moved away from Jet.
I'm going to guess wildly here that you field data types are of a numeric type? (Since you're performing arithmetic operations upon them.)
CDate((dbo.UT220AP.UTSSTM & [/] & dbo.UT220AP.UTSSTD & [/])
& 1900 + dbo.UT220AP.UTSSTC * 100 + dbo.UT220AP.UTSSTY) AS [Service Start]
probably should be
P.UTSSTM) + '/' + CONVERT(VARCHAR, dbo.UT220AP.UTSSTD) + '/' + CONVERT(VARCHAR, (1900 + dbo.UT220AP.UTSSTC * 100 + dbo.UT220AP.UTSSTY))) AS [Service Start]
But as I say - slightly guessing.