SQL ·
‹ Previous Next ›
⏱ 5 min read Modified: 2026-11-21

SQL Functions in MySQL: String, Numeric and Date Functions

A report needs the sale time as “2025-12-31 23:59”, and DATE_FORMAT(sold_at, '%Y-%m-%d %H:%M') looks right to anyone who writes Java date patterns. MySQL raises no error, but the result is 2025-12-31 23:December: in MySQL, %M is the month name and minutes are %i. Most function mistakes look like this — no error message, just a wrong string, number or date in the result.

SQL functions are built-in database operations that take arguments (columns, constants, expressions) and return a value. Single-row (scalar) functions are evaluated for each row separately and are used in SELECT, WHERE, ORDER BY, as well as in INSERT and UPDATE; aggregate functions (COUNT, SUM) collapse many rows into one value and are covered in the lesson on GROUP BY and HAVING. This lesson is about the single-row functions of MySQL 8: string functions, numeric functions, and date and time functions. The conditional expressions CASE and IF and the NULL functions COALESCE, IFNULL and NULLIF have their own lesson: CASE WHEN and COALESCE in SQL.

The sales Practice Table with a Sale Date

Every example in this lesson runs in MySQL 8 on the sales table from the lesson on the SELECT statement, with one extra column, sold_at — the date and time of the sale. It holds the same eight fruit sales in three grocery stores (quantities in pounds, prices per pound in US dollars); two items have no price yet (NULL), and the dates span two New Year’s Eves.

The script recreates the table, so if you changed sales in other lessons, the data goes back to the original:

DROP TABLE IF EXISTS sales;

CREATE TABLE sales (
    id      INT          NOT NULL AUTO_INCREMENT,
    shop    VARCHAR(50)  NOT NULL,
    product VARCHAR(50)  NOT NULL,
    lbs     INT          NOT NULL,
    price   DECIMAL(6,2) NULL,
    sold_at DATETIME     NOT NULL,
    PRIMARY KEY (id)
);

INSERT INTO sales (shop, product, lbs, price, sold_at) VALUES
    ('Acme',       'apples', 10, 1.50, '2024-12-30 18:45:00'),
    ('Acme',       'pears',  14, 2.50, '2025-01-15 09:30:00'),
    ('Acme',       'grapes', 20, NULL, '2025-01-31 20:10:00'),
    ('Delta',      'apples', 20, 1.75, '2025-02-03 11:00:00'),
    ('Delta',      'grapes', 34, 4.50, '2025-02-28 16:20:00'),
    ('Delta',      'lemons', 34, NULL, '2025-03-08 10:05:00'),
    ('Berry Hill', 'lemons', 14, 3.50, '2025-12-31 23:59:00'),
    ('Berry Hill', 'apples', 54, 1.25, '2026-01-02 08:15:00');
SELECT * FROM sales;
+----+------------+---------+-----+-------+---------------------+
| id | shop       | product | lbs | price | sold_at             |
+----+------------+---------+-----+-------+---------------------+
|  1 | Acme       | apples  |  10 |  1.50 | 2024-12-30 18:45:00 |
|  2 | Acme       | pears   |  14 |  2.50 | 2025-01-15 09:30:00 |
|  3 | Acme       | grapes  |  20 |  NULL | 2025-01-31 20:10:00 |
|  4 | Delta      | apples  |  20 |  1.75 | 2025-02-03 11:00:00 |
|  5 | Delta      | grapes  |  34 |  4.50 | 2025-02-28 16:20:00 |
|  6 | Delta      | lemons  |  34 |  NULL | 2025-03-08 10:05:00 |
|  7 | Berry Hill | lemons  |  14 |  3.50 | 2025-12-31 23:59:00 |
|  8 | Berry Hill | apples  |  54 |  1.25 | 2026-01-02 08:15:00 |
+----+------------+---------+-----+-------+---------------------+
8 rows in set

Why the sale date is a DATETIME and not a TIMESTAMP, and how the two types differ, is explained in the lesson on MySQL data types. You can also call a function without any table: SELECT UPPER('Java'); — a handy way to try the individual examples below.

MySQL String Functions

MySQL string functions change, cut, join and search text: CONCAT joins strings, SUBSTRING, LEFT and RIGHT extract part of a string, UPPER and LOWER change the case, TRIM removes spaces, REPLACE swaps a substring, and INSTR and LOCATE find its position. Character positions start at 1.

Function What it does Example Result
CHAR_LENGTH(str) Length of the string in characters CHAR_LENGTH('MySQL') 5
LENGTH(str) Length of the string in bytes LENGTH('MySQL') 5
INSTR(str, substr) Position of a substring in a string; 0 if not found INSTR('MySQL', 'SQL') 3
LOCATE(substr, str[, pos]) The same, but the substring comes first; the search can start at position pos LOCATE('a', 'Java', 3) 4
STRCMP(s1, s2) Compares strings: -1 if the first is smaller, 0 if they are equal, 1 if it is larger STRCMP('abc', 'def'), STRCMP('def', 'def'), STRCMP('def', 'abc') -1, 0, 1
CONCAT(s1, s2, ...) Joins strings CONCAT('abc', 'def') abcdef
REVERSE(str) Returns the string with the characters in reverse order REVERSE('Java') avaJ
LEFT(str, len) Returns the leftmost len characters of str LEFT('Java', 2) Ja
RIGHT(str, len) Returns the rightmost len characters of str RIGHT('Java', 2) va
LOWER(str) Returns str with all characters in lowercase, according to the string’s character set LOWER('Java') java
UPPER(str) Returns str with all characters in uppercase, according to the string’s character set UPPER('Java') JAVA
LPAD(str, len, padstr) Pads str on the left with padstr until it is len characters long LPAD('Java', 8, '.') ....Java
RPAD(str, len, padstr) Pads str on the right with padstr until it is len characters long RPAD('Java', 8, '.') Java....
TRIM(str) Returns the string without leading and trailing spaces TRIM(' Java ') Java
TRIM(LEADING 'N' FROM str) Removes the character N from the start (TRAILING — from the end, BOTH — from both sides) TRIM(LEADING 'N' FROM 'NNNNJavaNN') JavaNN
SUBSTRING(str, pos) Returns the substring of str starting at position pos; a negative pos counts from the end SUBSTRING('MySQL', 3), SUBSTRING('MySQL', -3) SQL, SQL
SUBSTRING(str, pos, len) Returns len characters of str starting at position pos SUBSTRING('MySQL', 3, 2) SQ
REPLACE(str, from, to) Replaces every occurrence of from with to REPLACE('MySQL 5.7', '5.7', '8.0') MySQL 8.0

INSTR and LOCATE take their arguments in opposite order: INSTR('MySQL', 'SQL') and LOCATE('SQL', 'MySQL') both return 3. Swapping them raises no error — the function simply looks for the string inside the substring and returns 0. STRCMP compares according to the collation, so in MySQL 8 STRCMP('java', 'JAVA') is 0: case is ignored by default. LPAD does not only pad, it also truncates: LPAD('JavaScript', 4, '.') returns Java.

One property of CONCAT catches people out in real data: if any argument is NULL, the whole result is NULL. How to keep the rest of the string with CONCAT_WS or COALESCE is shown in the section CONCAT and NULL.

SQL positions start at 1, not 0

In Java, "MySQL".indexOf("SQL") returns 2, while INSTR('MySQL', 'SQL') returns 3. “Not found” is different too: -1 in Java, 0 in MySQL. Logic ported from Java to SQL without the off-by-one fix cuts strings one character early, and SUBSTRING('MySQL', 0) quietly returns an empty string.

CHAR_LENGTH vs LENGTH: Characters and Bytes

CHAR_LENGTH counts characters, LENGTH counts bytes. For plain ASCII text the numbers match, but in the utf8mb4 character set an accented letter such as é takes 2 bytes and an emoji takes 4, so names with such characters get a larger LENGTH (bytes versus characters are covered in the section String Types). Use CHAR_LENGTH to validate input length and to truncate: VARCHAR(50) limits characters, not bytes.

String Functions on Table Columns

A function is called the same way for every value of a column. A five-digit product code, the name with a capital first letter and the length of the name for the Acme store:

SELECT LPAD(id, 5, '0')                                      AS code,
       CONCAT(UPPER(LEFT(product, 1)), SUBSTRING(product, 2)) AS title,
       CHAR_LENGTH(product)                                   AS chars,
       LENGTH(product)                                        AS bytes
FROM sales
WHERE shop = 'Acme';
+-------+--------+-------+-------+
| code  | title  | chars | bytes |
+-------+--------+-------+-------+
| 00001 | Apples |     6 |     6 |
| 00002 | Pears  |     5 |     5 |
| 00003 | Grapes |     6 |     6 |
+-------+--------+-------+-------+

The product names are plain ASCII, so characters and bytes match; a product called “café beans” would show 10 characters and 11 bytes. LPAD first converts the numeric id to a string implicitly. The table data itself does not change: functions in SELECT affect only the query result. To store the transformed value, put the same function into UPDATE ... SET product = TRIM(product).

Numeric Functions: ROUND, TRUNCATE, CEIL, FLOOR, MOD

MySQL numeric functions round and transform numbers: ROUND rounds to the nearest value, TRUNCATE drops extra decimal places without rounding, FLOOR and CEIL (CEILING) round down and up to an integer, MOD returns the remainder of a division, ABS the absolute value and SIGN the sign of a number.

Function What it does Example Result
ABS(x) Returns the absolute value ABS(-92), ABS(92) 92, 92
SIGN(x) Returns the sign of the argument as -1, 0 or 1, depending on whether X is negative, zero or positive SIGN(-92), SIGN(92), SIGN(0) -1, 1, 0
TRUNCATE(x, d) Returns X truncated to D decimal places. If D is 0, the result has no decimal point or fractional part TRUNCATE(92.55665, 2) 92.55
FLOOR(x) Rounds down to the nearest integer FLOOR(92.55665) 92
CEILING(x), CEIL(x) Rounds up to the nearest integer CEILING(92.55665) 93
ROUND(x) Rounds to the nearest integer ROUND(92.55665), ROUND(92.45) 93, 92
ROUND(x, d) Rounds to D decimal places; a negative D rounds to tens, hundreds ROUND(92.55665, 2), ROUND(1234.5, -2) 92.56, 1200
MOD(x, y), x % y Remainder of a division; takes the sign of the dividend MOD(10, 3), MOD(-10, 3) 1, -1
x DIV y Integer division 7 DIV 2 3
POWER(x, y), SQRT(x) Power and square root POWER(2, 10), SQRT(16) 1024, 4

The difference between ROUND and TRUNCATE shows up on prices with a 7% discount:

SELECT id, price,
       price * 0.93              AS discounted,
       ROUND(price * 0.93, 2)    AS rounded,
       TRUNCATE(price * 0.93, 2) AS truncated
FROM sales
WHERE shop = 'Acme';
+----+-------+------------+---------+-----------+
| id | price | discounted | rounded | truncated |
+----+-------+------------+---------+-----------+
|  1 |  1.50 |     1.3950 |    1.40 |      1.39 |
|  2 |  2.50 |     2.3250 |    2.33 |      2.32 |
|  3 |  NULL |       NULL |    NULL |      NULL |
+----+-------+------------+---------+-----------+

Multiplying a DECIMAL(6,2) by 0.93 gives 4 decimal places — the sum of the scales of the factors. Both discounted prices land exactly on a half cent, so the choice of function changes the price a customer pays: ROUND gives $1.40, TRUNCATE $1.39. Row 3 shows the rule that applies to almost every function in this lesson: if an argument is NULL, the result is NULL too. Replacing a missing price with zero or a default is done with COALESCE and IFNULL.

CEIL, DIV and MOD together solve a packing problem: how many 15-pound crates each sale needs, how many of them are full and how many pounds are left over:

SELECT id, lbs,
       CEIL(lbs / 15) AS crates,
       lbs DIV 15     AS full_crates,
       MOD(lbs, 15)   AS rest_lbs
FROM sales
WHERE shop = 'Delta';
+----+-----+--------+-------------+----------+
| id | lbs | crates | full_crates | rest_lbs |
+----+-----+--------+-------------+----------+
|  4 |  20 |      2 |           1 |        5 |
|  5 |  34 |      3 |           2 |        4 |
|  6 |  34 |      3 |           2 |        4 |
+----+-----+--------+-------------+----------+

What Will This Query Return?

SELECT ROUND(2.5) AS r1, ROUND(-2.5) AS r2, ROUND(2.5e0) AS r3,
       7 / 2 AS d1, 7 DIV 2 AS d2;
+----+----+----+--------+----+
| r1 | r2 | r3 | d1     | d2 |
+----+----+----+--------+----+
|  3 | -3 |  2 | 3.5000 |  3 |
+----+----+----+--------+----+

For exact-value numbers (DECIMAL and literals such as 2.5), MySQL rounds half away from zero: 2.5 → 3, -2.5 → -3. For approximate values (DOUBLE, the literal 2.5e0) the result depends on the C library, and on typical systems a half rounds to even — giving 2. Java behaves differently again: Math.round(-2.5) returns -2, because it rounds half up. The / operator in MySQL always produces a fractional result, 7 / 2 = 3.5000, whereas 7 / 2 between two ints in Java gives 3; integer division in SQL is DIV.

Date and Time Functions: NOW, CURDATE, DATE, YEAR, MONTH

MySQL date functions return the current moment (NOW, CURDATE, CURTIME) and extract parts of a date: DATE drops the time, YEAR, MONTH and DAY return the year, month and day, DAYOFWEEK and WEEKDAY return the day-of-week number, and DAYNAME its name.

SELECT NOW(), CURDATE(), CURTIME();
+---------------------+------------+-----------+
| NOW()               | CURDATE()  | CURTIME() |
+---------------------+------------+-----------+
| 2026-11-21 14:05:33 | 2026-11-21 | 14:05:33  |
+---------------------+------------+-----------+

The values depend on when the query runs and on the session time zone (time_zone). NOW() returns a DATETIME, CURDATE() a DATE, CURTIME() a TIME. The standard SQL synonyms are CURRENT_TIMESTAMP, CURRENT_DATE and CURRENT_TIME. NOW() captures the time the statement started and stays the same throughout one query, while SYSDATE() returns the time of the actual call. UTC_TIMESTAMP() gives the time in UTC regardless of the session settings.

Extracting date parts from the sold_at column:

SELECT id, sold_at,
       DATE(sold_at)      AS day,
       YEAR(sold_at)      AS y,
       MONTH(sold_at)     AS m,
       DAY(sold_at)       AS d,
       DAYOFWEEK(sold_at) AS dow,
       WEEKDAY(sold_at)   AS wd,
       DAYNAME(sold_at)   AS day_name
FROM sales
WHERE id IN (1, 2, 5, 6);
+----+---------------------+------------+------+------+------+------+------+-----------+
| id | sold_at             | day        | y    | m    | d    | dow  | wd   | day_name  |
+----+---------------------+------------+------+------+------+------+------+-----------+
|  1 | 2024-12-30 18:45:00 | 2024-12-30 | 2024 |   12 |   30 |    2 |    0 | Monday    |
|  2 | 2025-01-15 09:30:00 | 2025-01-15 | 2025 |    1 |   15 |    4 |    2 | Wednesday |
|  5 | 2025-02-28 16:20:00 | 2025-02-28 | 2025 |    2 |   28 |    6 |    4 | Friday    |
|  6 | 2025-03-08 10:05:00 | 2025-03-08 | 2025 |    3 |    8 |    7 |    5 | Saturday  |
+----+---------------------+------------+------+------+------+------+------+-----------+

The two day-of-week numberings are easy to mix up. DAYOFWEEK follows the ODBC standard: 1 is Sunday, 2 is Monday, 7 is Saturday. WEEKDAY counts from Monday starting at zero: 0 is Monday, 6 is Sunday. In Java, DayOfWeek.getValue() gives a third variant: Monday is 1, Sunday is 7. The most reliable way to pick weekend sales in MySQL is WHERE WEEKDAY(sold_at) >= 5 (in our data that is row 6, a Saturday). DAY() is a synonym for DAYOFMONTH(); there are also HOUR(), MINUTE(), QUARTER() and DAYOFYEAR().

DATE_FORMAT and STR_TO_DATE: Formatting Dates in MySQL

DATE_FORMAT(date, format) turns a date into a string of the shape you need, such as '12/31/2025 11:59 PM', and STR_TO_DATE(string, format) does the reverse — it parses a string into a date. The format is built from specifiers that start with %; all other characters (slashes, spaces, colons, commas) are printed as is.

Specifier Meaning Example for 2025-12-31 23:59:00
%Y Year, 4 digits 2025
%y Year, 2 digits 25
%m Month, 01–12 12
%c Month without a leading zero, 1–12 12
%M / %b Month name / abbreviated December / Dec
%d Day of the month, 01–31 31
%e Day of the month without a leading zero, 1–31 31
%W / %a Weekday name / abbreviated Wednesday / Wed
%H Hour, 00–23 23
%h and %p Hour 01–12 and AM/PM 11 PM
%i Minutes, 00–59 59
%s Seconds, 00–59 00
%j Day of the year, 001–366 365
SELECT id,
       DATE_FORMAT(sold_at, '%m/%d/%Y %h:%i %p') AS us_format,
       DATE_FORMAT(sold_at, '%b %e, %Y (%W)')    AS text_format
FROM sales
WHERE id IN (1, 7, 8);
+----+---------------------+--------------------------+
| id | us_format           | text_format              |
+----+---------------------+--------------------------+
|  1 | 12/30/2024 06:45 PM | Dec 30, 2024 (Monday)    |
|  7 | 12/31/2025 11:59 PM | Dec 31, 2025 (Wednesday) |
|  8 | 01/02/2026 08:15 AM | Jan 2, 2026 (Friday)     |
+----+---------------------+--------------------------+

Minutes in MySQL are %i, not %M. The mistake from the top of the lesson produces no error message; the month name simply shows up in the string: DATE_FORMAT('2025-12-31 23:59:00', '%Y-%m-%d %H:%M') returns 2025-12-31 23:December. Month and weekday names are printed in the language set by the lc_time_names variable (en_US by default); after SET lc_time_names = 'es_ES'; they come out in Spanish.

MySQL and Java format patterns do not match

The MySQL format '%m/%d/%Y %H:%i' corresponds to DateTimeFormatter.ofPattern("MM/dd/yyyy HH:mm") in Java. In Java, MM is the month and mm the minutes; in MySQL, %m is the month, %i the minutes and %M the month name. When you copy a pattern from one language to the other, check every letter.

DATE_FORMAT also works for grouping by period: '%Y-%m' turns a date into a month key. Pounds sold in each month (how SUM and GROUP BY work is explained in the lesson on GROUP BY):

SELECT DATE_FORMAT(sold_at, '%Y-%m') AS month, SUM(lbs) AS lbs
FROM sales
GROUP BY month
ORDER BY month;
+---------+------+
| month   | lbs  |
+---------+------+
| 2024-12 |   10 |
| 2025-01 |   34 |
| 2025-02 |   54 |
| 2025-03 |   34 |
| 2025-12 |   14 |
| 2026-01 |   54 |
+---------+------+

The '%Y-%m' format sorts correctly even as a string. With '%m/%Y', string sorting would put 01/2025 before 12/2024.

STR_TO_DATE is what you need when a date arrives as a string in someone else’s format, for example during a CSV import:

SELECT STR_TO_DATE('12/31/2025', '%m/%d/%Y')                   AS d1,
       STR_TO_DATE('12/31/2025 11:59 PM', '%m/%d/%Y %h:%i %p') AS d2,
       STR_TO_DATE('31/12/2025', '%m/%d/%Y')                   AS d3;
+------------+---------------------+------+
| d1         | d2                  | d3   |
+------------+---------------------+------+
| 2025-12-31 | 2025-12-31 23:59:00 | NULL |
+------------+---------------------+------+
1 row in set, 1 warning

The third string is a European day-first date, and month 31 does not exist. If a string does not match the format, STR_TO_DATE in a SELECT returns NULL with a warning (SHOW WARNINGS shows Incorrect datetime value), not an error. In an INSERT under strict mode the same string does raise an error — so catch bad dates before importing with a query like WHERE STR_TO_DATE(...) IS NULL.

DATE_ADD, DATE_SUB, DATEDIFF, TIMESTAMPDIFF, LAST_DAY

Date arithmetic in MySQL uses INTERVAL: DATE_ADD(date, INTERVAL 7 DAY) adds a period, DATE_SUB subtracts one, and the short form date + INTERVAL 1 MONTH is equivalent to DATE_ADD. The difference between dates is computed by DATEDIFF (in days, ignoring the time) and TIMESTAMPDIFF (in any unit); LAST_DAY returns the last day of the month.

Expression Result Comment
DATE_ADD('2025-01-31', INTERVAL 1 DAY) 2025-02-01 Crosses into the next month
DATE_ADD('2025-01-31', INTERVAL 1 MONTH) 2025-02-28 There is no February 31 — the day is clamped to the end of the month
DATE_ADD('2024-01-31', INTERVAL 1 MONTH) 2024-02-29 Leap year
DATE_SUB('2025-03-01', INTERVAL 1 DAY) 2025-02-28 Same as '2025-03-01' - INTERVAL 1 DAY
'2025-12-31 23:59:00' + INTERVAL 1 MINUTE 2026-01-01 00:00:00 Units: SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR
DATEDIFF('2025-03-01', '2025-02-01') 28 End date first, then start date
TIMESTAMPDIFF(MONTH, '2025-01-31', '2025-02-28') 0 Counts whole months only; order: unit, start, end
LAST_DAY('2025-02-10') 2025-02-28 In 2024 — 2024-02-29

Rows 2 and 7 of the table show an asymmetry: January 31 plus one month is February 28, yet TIMESTAMPDIFF between these two dates does not count a single whole month. In Java, LocalDate.of(2025, 1, 31).plusMonths(1) also gives 2025-02-28. On the table data — a payment due one month after the sale, and the end of the sale’s month:

SELECT id,
       DATE(sold_at)                      AS sold,
       DATE(sold_at + INTERVAL 1 MONTH)   AS pay_until,
       LAST_DAY(sold_at)                  AS month_end
FROM sales
WHERE id IN (3, 5);
+----+------------+------------+------------+
| id | sold       | pay_until  | month_end  |
+----+------------+------------+------------+
|  3 | 2025-01-31 | 2025-02-28 | 2025-01-31 |
|  5 | 2025-02-28 | 2025-03-28 | 2025-02-28 |
+----+------------+------------+------------+

The sale on February 28 is due on March 28, not March 31: INTERVAL 1 MONTH keeps the day number, not “end of month”. If the due date must be the last day of the following month, write LAST_DAY(sold_at + INTERVAL 1 MONTH). The first day of a date’s month is DATE_FORMAT(sold_at, '%Y-%m-01') or LAST_DAY(sold_at - INTERVAL 1 MONTH) + INTERVAL 1 DAY.

DATEDIFF compares dates only, while TIMESTAMPDIFF takes the time into account and drops incomplete units. How much time passed between sales 7 and 8 and midnight on January 10, 2026:

SELECT id, sold_at,
       DATEDIFF('2026-01-10', sold_at)             AS days_diff,
       TIMESTAMPDIFF(DAY, sold_at, '2026-01-10')  AS full_days,
       TIMESTAMPDIFF(HOUR, sold_at, '2026-01-10') AS hours
FROM sales
WHERE id IN (7, 8);
+----+---------------------+-----------+-----------+-------+
| id | sold_at             | days_diff | full_days | hours |
+----+---------------------+-----------+-----------+-------+
|  7 | 2025-12-31 23:59:00 |        10 |         9 |   216 |
|  8 | 2026-01-02 08:15:00 |         8 |         7 |   183 |
+----+---------------------+-----------+-----------+-------+

Sale 7 happened one minute before midnight, so 9 days and one minute separate it from January 10: DATEDIFF sees 10 calendar days, TIMESTAMPDIFF(DAY) sees 9 full days. For “how many days overdue” you usually want DATEDIFF; for “have 24 hours passed” — TIMESTAMPDIFF(HOUR). The argument order is opposite: DATEDIFF(end, start), but TIMESTAMPDIFF(unit, start, end).

Functions in WHERE and Indexes

A function in WHERE gives the correct result, but when it is applied to a column (WHERE YEAR(sold_at) = 2025), MySQL cannot use a regular index on that column and has to evaluate the function for every row of the table. Rewrite the condition so the column stays “bare” and the computation moves to the constant side: sold_at >= '2025-01-01' AND sold_at < '2026-01-01'.

An index stores the sold_at values sorted, so a date range can be found quickly. But the index holds no YEAR(sold_at) values, and to learn the year the server has to read every row. Both queries return the same 6 rows (ids 2–7), including the sale on December 31 at 23:59:

CREATE INDEX idx_sales_sold_at ON sales (sold_at);

-- function on the column: the index is not used
EXPLAIN SELECT * FROM sales WHERE YEAR(sold_at) = 2025;

-- range: the index can be used
EXPLAIN SELECT * FROM sales
WHERE sold_at >= '2025-01-01' AND sold_at < '2026-01-01';

In the first plan, possible_keys is NULL and type is ALL (a full table scan). In the second, possible_keys shows idx_sales_sold_at, and on a table with thousands of rows the plan becomes type: range. On eight rows the optimizer may still choose a full scan — it is cheaper than going through the index. The upper bound < '2026-01-01' is deliberate: BETWEEN '2025-01-01' AND '2025-12-31' would lose row 7; the reason is in the section The BETWEEN Operator.

Condition with a function How to rewrite it so the index works
YEAR(sold_at) = 2025 sold_at >= '2025-01-01' AND sold_at < '2026-01-01'
DATE(sold_at) = '2025-01-31' sold_at >= '2025-01-31' AND sold_at < '2025-02-01'
DATE_FORMAT(sold_at, '%Y-%m') = '2025-02' sold_at >= '2025-02-01' AND sold_at < '2025-03-01'
DATEDIFF(NOW(), sold_at) <= 30 sold_at >= CURDATE() - INTERVAL 30 DAY
LEFT(product, 3) = 'app' product LIKE 'app%' (a pattern without a leading % can use an index)
LOWER(shop) = 'acme' shop = 'acme': with the utf8mb4_0900_ai_ci collation, case is ignored anyway
lbs * 2 > 40 lbs > 20

If you really need to filter by a function often, MySQL 8.0.13 and later can build a functional index: CREATE INDEX idx_sales_year ON sales ((YEAR(sold_at))); — note the double parentheses. Such an index only helps queries with exactly the same expression, so a range on the original column is usually simpler. Functions in the SELECT list have no effect on index use.

MySQL Dates in Java: LocalDate and JDBC

In Java code, date functions show up most often in filtering by date: the range bounds are computed in Java and passed as LocalDate or LocalDateTime parameters, and the result is read back with getObject(..., LocalDate.class). Format dates for the user in Java with DateTimeFormatter rather than with DATE_FORMAT.

String sql = "SELECT id, product, DATE(sold_at) AS sold_day "
           + "FROM sales WHERE sold_at >= ? AND sold_at < ? ORDER BY sold_at";
LocalDate from = LocalDate.of(2025, 1, 1);
LocalDate to = from.plusMonths(1);                       // 2025-02-01, upper bound excluded
DateTimeFormatter fmt = DateTimeFormatter.ofPattern("MMM d, yyyy", Locale.US);

try (PreparedStatement ps = connection.prepareStatement(sql)) {
    ps.setObject(1, from.atStartOfDay());                // LocalDateTime for DATETIME
    ps.setObject(2, to.atStartOfDay());
    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            LocalDate day = rs.getObject("sold_day", LocalDate.class);
            System.out.println(rs.getInt("id") + " " + rs.getString("product") + " "
                    + day + " " + day.format(fmt));
        }
    }
}
// Output:
// 2 pears 2025-01-15 Jan 15, 2025
// 3 grapes 2025-01-31 Jan 31, 2025

Java computes the month bounds, and the SQL keeps a range condition on the bare column — the index on sold_at works, and you never glue a date into the query string. Since JDBC 4.2 (Connector/J 8), setObject and getObject(..., LocalDate.class) accept and return java.time types without going through java.sql.Date; a full DATETIME column is read the same way with getObject("sold_at", LocalDateTime.class). The full type mapping is in the section MySQL to Java Type Mapping. A date received as a LocalDate is formatted on the application side with DateTimeFormatter — with the right locale, and without strings in place of dates in the ResultSet. How to read a column that may be NULL (a missing price) is covered in the section NULL in Java: COALESCE in the Query or null in JDBC.

Where SQL Functions Trip People Up

Most mistakes with functions produce no error message: the query returns an empty string or a wrong number or date, and someone notices only in the report. The table lists cases not covered in the sections above, with MySQL 8 behavior and the fix.

Query What happens How to do it right
SUBSTRING('MySQL', 0, 2) An empty string: positions start at 1, there is no position 0 SUBSTRING('MySQL', 1, 2) → My
TRIM(name) on a value from a file Only spaces are removed; tabs and \r\n stay, and an = comparison misses the row TRIM(REPLACE(REPLACE(REPLACE(name, '\r', ''), '\n', ''), '\t', ''))
LPAD(code, 4, '0') for a 6-character code The code is silently cut to 4 characters Check CHAR_LENGTH(code) or allow a longer length
DATEDIFF(sold_at, NOW()) as “days since the sale” A negative number: the end of the period goes first DATEDIFF(NOW(), sold_at)
ORDER BY DATE_FORMAT(sold_at, '%m/%d/%Y') String sorting: 01/02/2026 comes before 12/30/2024 ORDER BY sold_at, and the format only in the SELECT list
SELECT shop, UPPER(shop) AS s FROM sales WHERE s = 'ACME' ERROR 1054: Unknown column 's' in 'where clause' Repeat the expression in WHERE; why the alias is not visible there is explained in SQL Query Execution Order

Mistakes with CASE, IF and NULL — a WHEN NULL branch that never fires, GREATEST with a NULL argument, || instead of CONCAT — are collected in the lesson CASE WHEN and COALESCE in SQL.

Frequently Asked Questions

Why do NOW() in MySQL and LocalDateTime.now() in Java show different times?

NOW() returns the time in the MySQL session time zone (the time_zone variable, by default the server operating system’s zone), while LocalDateTime.now() uses the JVM time zone. If the database server runs in UTC and the application in America/New_York, the clocks differ by 4 or 5 hours depending on daylight saving time. Fixes: keep UTC on both the server and the JVM, set the zone in the Connector/J property connectionTimeZone (serverTimezone in older versions), or pass the time from Java as a parameter instead of calling NOW() in the query.

How do I calculate age from a date of birth in MySQL?

TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) counts whole years: for a birth date of 1992-03-15 it returns 33 on 2026-03-14 and 34 on 2026-03-15. The DATEDIFF(CURDATE(), birth_date) / 365 variant goes wrong around birthdays because of leap years. In Java, Period.between(birthDate, LocalDate.now()).getYears() does the same.

How do I split a string by a delimiter in MySQL?

MySQL has no SPLIT function; use SUBSTRING_INDEX(string, delimiter, n): with a positive n it returns everything before the n-th delimiter from the left, with a negative n — from the right. SUBSTRING_INDEX('Java,SQL,Spring', ',', 2) returns Java,SQL, with -1 it returns Spring, and the second element comes from a nested call SUBSTRING_INDEX(SUBSTRING_INDEX('Java,SQL,Spring', ',', 2), ',', -1) — SQL. If a comma-separated list is permanently stored in one column, that breaks the first normal form — a separate table with one value per row is the better design.

How do I convert a Unix timestamp to a date in MySQL?

FROM_UNIXTIME(seconds) turns seconds since 1970-01-01 UTC into a DATETIME in the session time zone, and UNIX_TIMESTAMP(date) does the reverse. FROM_UNIXTIME(1735689600) returns 2025-01-01 00:00:00 with time_zone = '+00:00', but 2024-12-31 19:00:00 in a session set to America/New_York — the same number gives different dates. In Java, the same conversion is Instant.ofEpochSecond(1735689600), which is always UTC.

Comments

Please log in or register to have a possibility to add comment.