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?