SQL Server LOG() function

Summary: in this tutorial, you will learn how to use the SQL Server LOG() function to calculate the natural logarithm of a number.

Introduction to SQL Server LOG() function #

The natural logarithm is the logarithm to the base e, where e approximately equals 2.718. In the SQL server, you can use the LOG() function to calculate the natural logarithm of a specified number.

Here’s the syntax of the LOG() function:

LOG(n [, base])Code language: SQL (Structured Query Language) (sql)

In this syntax:

n is a float or a value that can implicitly converted to a float.
base specifies the base for the logarithm. It is optional and defaults to e.

The function returns NULL if either n or base is NULL.

SQL Server LOG() function examples #

Let’s take some examples of using the LOG() function.

1) Basic SQL Server LOG() function examples #

The following example uses the LOG() function to calculate the natural logarithm of 1:

SELECT LOG(1);Code language: SQL (Structured Query Language) (sql)

Output:

result
------
0

(1 row affected)Code language: SQL (Structured Query Language) (sql)

The following statement uses the LOG() function to calculate the natural logarithm of e:

SELECT LOG(EXP(1)) result;Code language: SQL (Structured Query Language) (sql)

Output:

result
-------
1Code language: SQL (Structured Query Language) (sql)

The following example calculates the natural logarithm of the number 10:

SELECT LOG(10) result;Code language: SQL (Structured Query Language) (sql)

Output:

result
----------------
2.30258509299405Code language: SQL (Structured Query Language) (sql)

The following example uses the LOG() function to calculate the logarithm of 8 with base 3:

SELECT LOG(8,2) result;Code language: SQL (Structured Query Language) (sql)

Output:

result
------
3Code language: SQL (Structured Query Language) (sql)

2) Using LOG() function to calculate compound interest #

Suppose you have an initial value P, the annual interest rate is r% per year, after n year, you’ll receive F amount.

After one year, you’ll receive F = P(1 + r)
After two years, you’ll receive F = P(1+ r) (1+r) = P (1+ r)²
After n years, you’ll receive F = P (1 + r)ⁿ

If you have 1000 USD, the annual interest rate is 5%, and if you want to receive 2000, you’ll have to wait for n years, where n is: n = log(F/P) / log(1 +r) = log(1.5)/log(1.05) ~ 8.31 years.

In SQL server, you can use the LOG function to do it:

DECLARE @p decimal = 1000;
DECLARE @f decimal = 1500;
DECLARE @r decimal = 5; 
DECLARE @n decimal(5,2);

SET @n = LOG(@f/@p)/ LOG(1 + @r/100);

SELECT @n years;Code language: SQL (Structured Query Language) (sql)

Output:

years
------
8.31Code language: SQL (Structured Query Language) (sql)

3) Using LOG() function with table data #

First, create a new table called investments to store the present value, future value, and average annual interest rate:

CREATE TABLE investments (
    id INT IDENTITY PRIMARY KEY,
    present_value DECIMAL(19, 2) NOT NULL,
    future_value DECIMAL(19, 2) NOT NULL,
    annual_interest_rate DECIMAL(19, 2) NOT NULL
);Code language: SQL (Structured Query Language) (sql)

Second, insert some rows into the investments table:

INSERT INTO
  investments (present_value, future_value, annual_interest_rate)
VALUES
  (1000, 1500, 0.05),
  (2000, 5000, 0.06),
  (5000, 10000, 0.1);Code language: SQL (Structured Query Language) (sql)

Third, retrieve data from the investments table:

SELECT * FROM investments;Code language: SQL (Structured Query Language) (sql)

Finally, calculate the years to take to receive the future value from the present value and the annual interest rate:

SELECT
  id,
  present_value,
  future_value,
  annual_interest_rate,
  CAST(
    ROUND(
      LOG(future_value / present_value) / LOG(1 + annual_interest_rate),
      2
    ) AS DEC (5, 2)
  ) years
FROM
  investments;Code language: SQL (Structured Query Language) (sql)

Output:

id | present_value | future_value | annual_interest_rate | years
---+---------------+--------------+----------------------+-------
1  | 1000.00       | 1500.00      | 0.05                 | 8.31
2  | 2000.00       | 5000.00      | 0.06                 | 15.73
3  | 5000.00       | 10000.00     | 0.10                 | 7.27
(3 rows)Code language: SQL (Structured Query Language) (sql)

Summary #

Use the LOG() function to calculate the natural logarithm of a number.

Was this tutorial helpful?