Calculating interest between two dates - which is the best data type?

  • I have a table with a "from date", a "to date", and a total interest amount. I need to work out, on a simple interest basis, the proportion of interest applicable to a subset of days within this range. Basically I'm using datediff to get the number of days between the two, then calculating the number of days from my own starting date to the to date and dividing this by the total to get the fraction to multiply by the interest applied.

    For example,

    From Date = 01 Oct 2008

    To Date = 31 Oct 2008

    Interest amount = £1000

    Interest applied from 15 Oct 2008 to 31 Oct 2008 = ((days between 15 oct 2008 and 31 Oct 2008)/(days between 01 oct 2008 and 31 oct 2008)) * £1000.

    My query is, which SQL data type is best to use for these calculations? I will then cast the final value as a decimal(18.2) or a money field.

  • Griff, since nobody stepped up, I will ask a couple of questions. I don't think you require floating point operations here, but how do you define best? Are you talking about the datatype to store the source or result data? For money amounts that is usually money, or numeric, to two decimals (obviously). You can cast or convert these on the fly if you want to calculate interest to 3 or more decimal places before you start rounding, but is that your intention?

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • I would want the most accurate really. My problem is that datediff returns an integer and if I've got a datediff divided by a datediff with the numerator part of the fraction smaller than the denominator I can quite easily get a zero. I multiply this by the daily interest amount and I get zero which is obviously wrong. As a result I have to cast the datediff as a another data type, but when you're dealing with interest amounts these need to be quite precise as otherwise the end figures can vary quite a lot. I've gone with a real data type in my two casts for now. Would you agree with this? I then cast the result of that division being multiplied by the daily rate as a decimal with two decimal places.

  • You could try casting the datediff output from integer to a more precise decimal format. That way you don't lose precision early in the calculation and you can even capture fractional pence if necessary.

  • OK. I'll try decimal(18,6) I think. Thanks for your help.

  • You want to use DECIMAL with sufficient significant digits (scale) based upon the values being calculated. You did not specify the number of digits of the interest rate percentage. E.g., is it 5%, 5.1%, 5.25%, 5.125%, 5.0625%, etc.

    As one who has had to deal with "interesting" rounding problems in the financial services sector, I'd say that 6 decimal places may not be enough. I had a case once where we needed over 20 decimal places!

    In this computation, I'd personally use something like DECIMAL(38,23) (will support up to 999 trillion) for any intermediate variable. Then you can round and/or truncate to the final target field.


    [font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

  • The bigger question is - what do you plan on doing with the results? If you plan on, say, storing the interest earned in a day, and then start adding up days to get to month/day/year totals, then you should NOT use a precise data type like decimal, since it will introduce a bigger error than would something like float.

    In general, if you plan on doing ANY calculations based on the results you store, stick with float (and probably the "widest" float you can manage). Why presume you know how many "places" will be needed? Store as many as you are allowed to, cutting down to the appropriate size once you're ready to display the result.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Very useful responses. Many thanks.

  • Wow. Talk about a blast from the past..... 🙂

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • The Dixie Flatline (5/28/2014)


    Wow. Talk about a blast from the past..... 🙂

    The thread looks very interesting. Too bad it got hit with payday loan spam.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Looks like it's been cleaned up already.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Viewing 11 posts - 1 through 10 (of 10 total)

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