SQL Server does a very strange stuff with precision of the result when doing basic math operations (*, /). I did a research and to put it short, use DECIMAL(25,13) type if you want to retain precision, or generally, DECIMAL(p,s) where p+s = 38!

REAL is not a precise type, it's a floating point type.

If you return money, be aware that you actually rounded the result to 4 decimal places.

So, you should probably go with something like this:

CREATE FUNCTION [dbo].VAT (

@Net_Amount [money],

@Order_Date [date] )

RETURNS money

AS

BEGIN

DECLARE @VAT_Rate DECIMAL(25,13) =

CASE

WHEN @Order_Date >= '2011-01-04' then 0.200

WHEN @Order_Date >= '2010-01-01' then 0.175

WHEN @Order_Date >= '2008-12-01' then 0.150

WHEN @Order_Date >= '1991-01-01' then 0.175

WHEN @Order_Date >= '1979-06-18' then 0.150

WHEN @Order_Date >= '1974-07-01' then 0.080

WHEN @Order_Date >= '1973-04-01' then 0.100

ELSE 0.000

END

RETURN CONVERT( DECIMAL(25,13), @Net_Amount ) * @VAT_Rate

END

GO