SQL Functions · Lesson 8 of 30

SQL Numeric and Date Functions

5 min read Updated Nov 7, 2026 Saurabh Samir

On this page

Numeric functions
Extracting parts of a date
Formatting dates
Date arithmetic
Practice
Summary

Reports are full of numbers and dates: revenue rounded to two decimals, orders per month, days since signup, delivery due dates. SQL can calculate all of these directly. This lesson covers the numeric functions you'll use for money and maths, then the date functions behind almost every business report.

In this lesson

Rounding and maths: ROUND, CEIL, FLOOR, MOD, ABS
Integer vs decimal division
Extracting and formatting dates
Date arithmetic: DATEDIFF, DATE_ADD, TIMESTAMPDIFF

Numeric functions

SELECT ROUND(1234.5678, 2)  AS round_2,
       ROUND(1234.5678)     AS round_0,
       ROUND(1234.5678, -2) AS round_hundreds,
       CEIL(4.1)            AS ceil_,
       FLOOR(4.9)           AS floor_,
       MOD(17, 5)           AS remainder,
       ABS(-250)            AS absolute,
       POWER(2, 10)         AS power_;

Output

+---------+---------+----------------+-------+--------+-----------+----------+--------+
| round_2 | round_0 | round_hundreds | ceil_ | floor_ | remainder | absolute | power_ |
+---------+---------+----------------+-------+--------+-----------+----------+--------+
| 1234.57 |    1235 |           1200 |     5 |      4 |         2 |      250 |   1024 |
+---------+---------+----------------+-------+--------+-----------+----------+--------+

A practical use: how many pages does a list of 10 products need at 4 per page? CEIL(10 / 4).

SELECT COUNT(*) AS products, CEIL(COUNT(*) / 4) AS pages_of_4 FROM products;

Output

+----------+------------+
| products | pages_of_4 |
+----------+------------+
|       10 |          3 |
+----------+------------+

Division

SELECT 7 / 2 AS decimal_division, 7 DIV 2 AS integer_division, 7 % 2 AS remainder;

Output

+------------------+------------------+-----------+
| decimal_division | integer_division | remainder |
+------------------+------------------+-----------+
|           3.5000 |                3 |         1 |
+------------------+------------------+-----------+

Integer division differs between databases

In MySQL, 7 / 2 is 3.5. In PostgreSQL and SQL Server, dividing two integers truncates: 7 / 2 is 3. To be safe everywhere, multiply by 1.0 first: 7 * 1.0 / 2. Always store money as DECIMAL, never FLOAT, to avoid rounding errors.

Extracting parts of a date

SELECT order_id,
       order_date,
       YEAR(order_date)       AS yr,
       MONTH(order_date)      AS mon,
       MONTHNAME(order_date)  AS month_name,
       DAYNAME(order_date)    AS weekday,
       QUARTER(order_date)    AS qtr
FROM orders
WHERE order_id IN (101, 106, 110);

Output

+----------+------------+------+------+------------+----------+------+
| order_id | order_date | yr   | mon  | month_name | weekday  | qtr  |
+----------+------------+------+------+------------+----------+------+
|      101 | 2025-08-02 | 2025 |    8 | August     | Saturday |    3 |
|      106 | 2025-09-15 | 2025 |    9 | September  | Monday   |    3 |
|      110 | 2025-11-02 | 2025 |   11 | November   | Sunday   |    4 |
+----------+------------+------+------+------------+----------+------+

Formatting dates

SELECT order_id,
       DATE_FORMAT(order_date, '%d %b %Y')    AS indian_style,
       DATE_FORMAT(order_date, '%W, %M %e')   AS long_style,
       DATE_FORMAT(order_date, '%Y-%m')       AS yyyy_mm
FROM orders
WHERE order_id IN (101, 112);

Output

+----------+--------------+----------------------+---------+
| order_id | indian_style | long_style           | yyyy_mm |
+----------+--------------+----------------------+---------+
|      101 | 02 Aug 2025  | Saturday, August 2   | 2025-08 |
|      112 | 06 Nov 2025  | Thursday, November 6 | 2025-11 |
+----------+--------------+----------------------+---------+

Code	Meaning	Code	Meaning
`%Y`	4-digit year	`%d`	day 01–31
`%m`	month 01–12	`%e`	day 1–31
`%b` / `%M`	Nov / November	`%a` / `%W`	Thu / Thursday
`%H:%i:%s`	24-hour time	`%h:%i %p`	12-hour time with AM/PM

(PostgreSQL uses TO_CHAR(order_date, 'DD Mon YYYY'); SQL Server uses FORMAT.)

Date arithmetic

The examples below use a fixed "today" of 7 November 2025 so the results never change; in real queries you'd use CURDATE() (date) or NOW() (date and time).

SELECT name,
       signup_date,
       DATEDIFF('2025-11-07', signup_date)              AS days_as_customer,
       TIMESTAMPDIFF(MONTH, signup_date, '2025-11-07')  AS full_months,
       DATE_ADD(signup_date, INTERVAL 1 YEAR)           AS renewal_date
FROM customers
WHERE customer_id IN (1, 5, 8);

Output

+------------+-------------+------------------+-------------+--------------+
| name       | signup_date | days_as_customer | full_months | renewal_date |
+------------+-------------+------------------+-------------+--------------+
| Asha Verma | 2025-01-15  |              296 |           9 | 2026-01-15   |
| Zoya Khan  | 2025-04-02  |              219 |           7 | 2026-04-02   |
| Vikram Das | 2025-07-30  |              100 |           3 | 2026-07-30   |
+------------+-------------+------------------+-------------+--------------+

-- Expected delivery: 5 days after ordering; flag orders still not delivered after that
SELECT order_id, order_date, status,
       order_date + INTERVAL 5 DAY AS expected_by,
       CASE WHEN status IN ('pending', 'shipped')
             AND '2025-11-07' > order_date + INTERVAL 5 DAY THEN 'LATE' ELSE '' END AS flag
FROM orders
WHERE status IN ('pending', 'shipped');

Output

+----------+------------+---------+-------------+------+
| order_id | order_date | status  | expected_by | flag |
+----------+------------+---------+-------------+------+
|      106 | 2025-09-15 | shipped | 2025-09-20  | LATE |
|      108 | 2025-10-10 | pending | 2025-10-15  | LATE |
|      109 | 2025-10-21 | shipped | 2025-10-26  | LATE |
|      111 | 2025-11-05 | pending | 2025-11-10  |      |
+----------+------------+---------+-------------+------+

Filtering by date ranges

-- Orders in October 2025: a half-open range keeps it index-friendly and exact
SELECT order_id, order_date
FROM orders
WHERE order_date >= '2025-10-01' AND order_date < '2025-11-01';

Output

+----------+------------+
| order_id | order_date |
+----------+------------+
|      107 | 2025-10-03 |
|      108 | 2025-10-10 |
|      109 | 2025-10-21 |
+----------+------------+

Prefer ranges to functions on dates

WHERE MONTH(order_date) = 10 gives the same rows here but can't use an index on order_date and silently matches October of every year. A range is faster and says exactly what you mean.

Practice

Try it yourself

For each employee, show the hire year and how many full years they had worked on 2025-11-07.

Show solution

SELECT name, YEAR(hire_date) AS hired, TIMESTAMPDIFF(YEAR, hire_date, '2025-11-07') AS years_worked
FROM employees
ORDER BY hire_date
LIMIT 4;

Output

+-------------+-------+--------------+
| name        | hired | years_worked |
+-------------+-------+--------------+
| Anita Desai |  2018 |            7 |
| Rohit Mehta |  2019 |            6 |
| Farhan Ali  |  2019 |            5 |
| Kavya Reddy |  2020 |            5 |
+-------------+-------+--------------+

Show the price of each Book with 18% GST, rounded up to the next whole rupee.

Show solution

SELECT name, price, CEIL(price * 1.18) AS price_with_gst
FROM products
WHERE category_id = 2;

Output

+-------------------+--------+----------------+
| name              | price  | price_with_gst |
+-------------------+--------+----------------+
| Clean Code        | 450.00 |            531 |
| SQL in 10 Minutes | 350.00 |            413 |
| JavaScript Guide  | 550.00 |            649 |
+-------------------+--------+----------------+

Quick check

What does ROUND(2.5) give in MySQL?

3 – exact DECIMAL values round half away from zero.

Which function gives the number of days between two dates?

DATEDIFF(later, earlier) in MySQL.

Why is WHERE YEAR(order_date) = 2025 slower than a range?

The function on the column prevents index use, forcing a full scan.

Summary

ROUND, CEIL, FLOOR, MOD and ABS cover most maths; store money as DECIMAL.
YEAR, MONTH, DAYNAME and DATE_FORMAT extract and format dates.
DATEDIFF, TIMESTAMPDIFF and INTERVAL do date arithmetic; filter with half-open ranges.

Finished this lesson?

Mark it complete to track your progress, then carry on with the next lesson.

Next lesson