I want to report the VAT rate to 2 decimal places - I can't just use DECIMAL(19,2)  (well I could but I'd have to rewrite the reporting tool that thinks BCD fields are currency. I've achieved what I want using

CAST(TRUNC("VAT Rate") AS VARCHAR(3))+'.'+CAST(CAST(100*("VAT Rate" - TRUNC("VAT Rate")) AS VARCHAR(2))+'00' AS VARCHAR(2)) AS "VAT Rate",

but I can't help thinking there's a better way - any of you lot know one?


Roy Lambert

Roy,

You will have to be more specific, what's the original type of the column "VAT Rate" and what reporting tool you using?

--
Fernando Dias
[Team Elevate]

Fernando


Forget the reporting tool its not a commercial one which is why I could re-write it if I wanted to take the time.

Original type of VAT Rate was DECIMAL(19,2) but that's been changed to FLOAT. What I have works I'm just hoping for a more elegant way.

Roy Lambert

In a similar situation I multiply by 100, round to 0 decimal places, then divide by 100. This gives a float with 2 Decimals. I think you would then cast this to VARCHAR to get what you want. It is not much more elegant TBH.

CAST(ROUND(MyField * 100, 0) / 100) as VARCHAR(20));