Number and date functions
45 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)Most business questions are sums over money and time: GST rounded to paise, buses needed for a trip, days a library book is late, years an employee has worked. MySQL has functions for each, and they run inside the query, on every row.
ROUND, TRUNCATE, CEIL, FLOOR and DIV
ROUND(x, d) rounds to d decimal places, TRUNCATE(x, d) cuts the extra digits, CEIL(x) goes up to a whole number and FLOOR(x) goes down. a DIV b keeps the whole part of a division and a % b the remainder.
A school trip needs whole buses, and an invoice needs paise. The first example splits 130 students into 40-seat buses; the second rounds the GST on ShopLite prices.
Example 1 · GST, buses and the last bus
DROP TABLE IF EXISTS college_trip;CREATE TABLE college_trip ( branch VARCHAR(10) PRIMARY KEY, students INT NOT NULL, fee_total DECIMAL(10,2) NOT NULL);INSERT INTO college_trip VALUES ('CSE', 130, 245000.00), ('ECE', 82, 151700.00), ('MECH', 40, 73999.00);
SELECT branch, students, CEIL(students / 40) AS buses_needed, students DIV 40 AS full_buses, students % 40 AS in_last_bus, ROUND(fee_total / students, 2) AS fee_per_head, TRUNCATE(fee_total / students, 0) AS whole_rupeesFROM college_tripORDER BY branch;+--------+----------+--------------+------------+-------------+--------------+--------------+ | branch | students | buses_needed | full_buses | in_last_bus | fee_per_head | whole_rupees | +--------+----------+--------------+------------+-------------+--------------+--------------+ | CSE | 130 | 4 | 3 | 10 | 1884.62 | 1884 | | ECE | 82 | 3 | 2 | 2 | 1850.00 | 1850 | | MECH | 40 | 1 | 1 | 0 | 1849.98 | 1849 | +--------+----------+--------------+------------+-------------+--------------+--------------+
What this code does
- CSE has 130 students:
130 / 40is 3.25,CEILmakes it 4 buses,DIVgives 3 full buses and%the 10 students in the fourth. MECH's 40 fit exactly, so the remainder is 0. ROUND(..., 2)gives the fee per head in rupees and paise (73999 / 40 = 1849.975 rounds up to 1849.98).TRUNCATE(..., 0)just drops the paise.
Example 2 · GST on ShopLite prices
SELECT name, price, price * 0.18 AS gst_raw, ROUND(price * 0.18, 2) AS gst, ROUND(price, -3) AS approx_priceFROM productsWHERE category_id = 4ORDER BY product_id;+------------------------+---------+----------+--------+--------------+ | name | price | gst_raw | gst | approx_price | +------------------------+---------+----------+--------+--------------+ | boAt Airdopes 141 | 1299.00 | 233.8200 | 233.82 | 1000 | | Mi Power Bank 20000mAh | 2199.00 | 395.8200 | 395.82 | 2000 | | Logitech M331 Mouse | 849.00 | 152.8200 | 152.82 | 1000 | +------------------------+---------+----------+--------+--------------+
What this code does
price * 0.18keeps four decimal places;ROUND(..., 2)gives an amount in rupees and paise that can go on an invoice.ROUND(price, -3)rounds to the nearest thousand, handy for a "about Rs. 2,000" label: 849 becomes 1000, and 1299 becomes 1000.
GST of 18% on 1299 is 233.82 after ROUND. 130 students need CEIL(130 / 40) = 4 buses, because 3.25 buses is not possible; 130 DIV 40 = 3 full buses and 130 % 40 = 10 students in the last one. A negative d rounds left of the point: ROUND(1234.567, -2) is 1200.
ROUND(19.99)= 20,TRUNCATE(19.99, 0)= 19.CEIL(3.25)= 4,FLOOR(3.25)= 3,FLOOR(-2.5)= -3 (down means towards minus).- Halves: the rule above holds for exact
DECIMALvalues. For approximateFLOAT/DOUBLEvalues the result can depend on the system, which is one more reason to store money as DECIMAL.
CAST and implicit conversion surprises
CAST(value AS type) converts a value to another type on purpose. Implicit conversion is MySQL changing a type by itself, which can give quiet wrong answers, such as '10' < '9' being true.
When you do not convert, MySQL converts for you, and the results can surprise. The query below compares text with text, text with a number, and then converts on purpose with CAST.
Example 3 · Implicit conversion surprises, and CAST
SELECT '10' < '9' AS text_compare, 10 < 9 AS number_compare, 'abc' = 0 AS text_equals_zero, CAST('42' AS UNSIGNED) + 1 AS cast_number, CAST(17999.50 AS SIGNED) AS cast_rounds, 0.1 + 0.2 = 0.3 AS decimal_exact, 0.1e0 + 0.2e0 AS double_sum;
SELECT '5 apples' + 3 AS surprise;SHOW WARNINGS;+--------------+----------------+------------------+-------------+-------------+---------------+---------------------+ | text_compare | number_compare | text_equals_zero | cast_number | cast_rounds | decimal_exact | double_sum | +--------------+----------------+------------------+-------------+-------------+---------------+---------------------+ | 1 | 0 | 1 | 43 | 18000 | 1 | 0.30000000000000004 | +--------------+----------------+------------------+-------------+-------------+---------------+---------------------+ +----------+ | surprise | +----------+ | 8 | +----------+ +---------+------+----------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------+ | Warning | 1292 | Truncated incorrect DOUBLE value: '5 apples' | +---------+------+----------------------------------------------+
What this code does
'10' < '9'is true because text compares character by character and1comes before9.'abc' = 0is true because MySQL turns'abc'into the number 0.CAST(17999.50 AS SIGNED)rounds to 18000.'5 apples' + 3reads the 5 and gives 8. MySQL does not stop; it only records a warning, whichSHOW WARNINGSdisplays. An application rarely looks at warnings, so such bugs go unnoticed.
Text is compared letter by letter, so '10' < '9' is true. Text used as a number is read up to the first non-digit: '5 apples' + 3 is 8 with a warning, and 'abc' = 0 is true. Keep numbers in number columns, compare numbers with numbers, and use CAST when a value really arrives as text.
DECIMAL is exact, so 0.1 + 0.2 = 0.3 is true for decimal values. In DOUBLE it gives 0.30000000000000004. Keep rupee amounts in DECIMAL columns.DATE_FORMAT, YEAR, MONTH and DAYNAME
DATE_FORMAT(date, format) turns a date into text in any layout, using codes such as %d (day), %m (month), %b (Jan) and %Y (2025). YEAR, MONTH and DAYNAME pull out single parts of a date.
MySQL shows dates as YYYY-MM-DD, but an Indian invoice wants 05-01-2025. The query below formats each order date and pulls out its year, month and weekday.
Example 4 · Formatting order dates for an invoice
SELECT order_id, order_date, DATE_FORMAT(order_date, '%d-%m-%Y') AS indian_style, DATE_FORMAT(order_date, '%d %b %Y') AS invoice_date, YEAR(order_date) AS yr, MONTH(order_date) AS mon, DAYNAME(order_date) AS weekdayFROM ordersWHERE customer_id = 1ORDER BY order_date;+----------+------------+--------------+--------------+------+------+----------+ | order_id | order_date | indian_style | invoice_date | yr | mon | weekday | +----------+------------+--------------+--------------+------+------+----------+ | 1 | 2025-01-05 | 05-01-2025 | 05 Jan 2025 | 2025 | 1 | Sunday | | 3 | 2025-02-02 | 02-02-2025 | 02 Feb 2025 | 2025 | 2 | Sunday | | 10 | 2025-04-12 | 12-04-2025 | 12 Apr 2025 | 2025 | 4 | Saturday | +----------+------------+--------------+--------------+------+------+----------+
What this code does
order_dateis shown by MySQL as2025-01-05.DATE_FORMATbuilds any other layout as text, such as05-01-2025for an Indian invoice.YEAR,MONTHandDAYNAMEpull out parts of the date. The rows are sorted by the realorder_date, not by the formatted text.
Other codes: %M is the month name and %W the weekday name. Format only for display: DATE_FORMAT returns text, so sorting by '%d-%m-%Y' puts 01-02-2025 before 05-01-2025. Sort by the real date column.
DATE_FORMAT(order_date, '%d-%m-%Y')→05-01-2025.DATE_FORMAT(order_date, '%Y-%m')→2025-01, a month key that also sorts correctly.DAYNAME('2025-01-05')→Sunday.
DATEDIFF, DATE_ADD and TIMESTAMPDIFF
DATEDIFF(later, earlier) returns the days between two dates, DATE_ADD(date, INTERVAL n unit) moves a date forward, and TIMESTAMPDIFF(unit, start, end) counts complete units, such as full years.
A library needs due dates and late days; HR needs years of service. The first query adds 14 days to each issue date and counts the days late; the second counts complete years with TIMESTAMPDIFF.
Example 5 · Due dates and late days in a library
DROP TABLE IF EXISTS library_loans;CREATE TABLE library_loans ( loan_id INT PRIMARY KEY, student VARCHAR(30) NOT NULL, issued_on DATE NOT NULL, returned_on DATE NULL);INSERT INTO library_loans VALUES (1, 'Aditya', '2025-06-02', '2025-06-12'), (2, 'Bhavana', '2025-06-05', '2025-06-25'), (3, 'Chirag', '2025-06-10', NULL);
-- A book is due 14 days after issue. "Today" is fixed as 2025-06-30.SELECT student, issued_on, DATE_ADD(issued_on, INTERVAL 14 DAY) AS due_on, returned_on, DATEDIFF(IFNULL(returned_on, '2025-06-30'), DATE_ADD(issued_on, INTERVAL 14 DAY)) AS days_lateFROM library_loansORDER BY loan_id;+---------+------------+------------+-------------+-----------+ | student | issued_on | due_on | returned_on | days_late | +---------+------------+------------+-------------+-----------+ | Aditya | 2025-06-02 | 2025-06-16 | 2025-06-12 | -4 | | Bhavana | 2025-06-05 | 2025-06-19 | 2025-06-25 | 6 | | Chirag | 2025-06-10 | 2025-06-24 | NULL | 6 | +---------+------------+------------+-------------+-----------+
What this code does
DATE_ADD(issued_on, INTERVAL 14 DAY)gives each due date.DATEDIFF(returned, due)counts days; a negative number means the book came back early.- Chirag has not returned his book, so
IFNULLuses the fixed "today" date. In a real app this would beCURDATE(), so the late days grow each day.
Example 6 · Years of service with TIMESTAMPDIFF
SELECT name, hire_date, TIMESTAMPDIFF(YEAR, hire_date, '2025-06-30') AS full_years, TIMESTAMPDIFF(MONTH, hire_date, '2025-06-30') AS full_months, DATEDIFF('2025-06-30', hire_date) AS daysFROM employeesWHERE dept_id = 3 OR dept_id IS NULLORDER BY hire_date;+----------------+------------+------------+-------------+------+ | name | hire_date | full_years | full_months | days | +----------------+------------+------------+-------------+------+ | Lakshmi Menon | 2018-09-09 | 6 | 81 | 2486 | | Rohan Das | 2024-05-20 | 1 | 13 | 406 | | Tanvi Kulkarni | 2025-01-06 | 0 | 5 | 175 | +----------------+------------+------------+-------------+------+
What this code does
TIMESTAMPDIFF(YEAR, ...)counts complete years only: Rohan Das joined on 2024-05-20, so by 2025-06-30 he has 1 full year, not 1.1.- The unit decides the answer: the same dates give 13 full months or 406 days. HR uses full years for gratuity and leave rules, so this is the right function, not
DATEDIFF / 365.
SQL handles month lengths and leap years for you. CURDATE() gives today's date and NOW() the date and time, so a real app writes DATEDIFF(CURDATE(), issued_on). The examples use fixed dates, so their results never change.
DATEDIFF('2025-03-01', '2025-02-01')= 28. Put the later date first, or the answer is negative.DATE_ADD('2025-01-31', INTERVAL 1 MONTH)=2025-02-28: MySQL uses the last valid day.TIMESTAMPDIFF(YEAR, '2015-06-01', '2025-05-31')= 9, because the 10th year is one day short.
Predict the output
Work out the date result
Read the code and pick what it prints.
Round 1 of 3
Put the later date first.
Next step
Check your understanding
12 quick questions