Essential SQL Functions for Data Manipulation

Date and Time Functions

DATE_FORMAT

The DATE_FORMAT function allows you to display date/time values in different formats.

-- Function: DATE_FORMAT
SELECT DATE_FORMAT(CURRENT_TIMESTAMP(), "%Y-%m-%d %H:%i:%s") AS formatted_time,
       DATE_FORMAT(creation_date, "%m - %w") AS formatted_date
FROM employees;

FROM_UNIXTIME

The FROM_UNIXTIME function converts UNIX timestamps to a date and time format. A UNIX timestamp represents the number of seconds since January 1, 1970 (UTC).

-- Function: FROM_UNIXTIME(unix_timestamp)
-- Note: The unix_timestamp value must be in seconds; milliseconds will cause conversion failure
SELECT DATE_FORMAT(FROM_UNIXTIME(unix_timestamp), "%Y-%m-%d %H:%i:%s") AS datetime
FROM system_logs;

UNIX_TIMESTAMP

The UNIX_TIMESTAMP function returns a UNIX timestamp, which is the number of seconds since '1970-01-01 00:00:00'.

-- Function: UNIX_TIMESTAMP
SELECT UNIX_TIMESTAMP(creation_date), UNIX_TIMESTAMP(NOW()) FROM employees;

DATE_SUB

The DATE_SUB function subtracts a specified time interval from a date. It takes three parameters: the date/time value, the interval value, and the interval unit.

-- Function: DATE_SUB
-- 1. Subtract 7 days from the current date
SELECT DATE_SUB(DATE_FORMAT(CURRENT_DATE(), "%Y-%m-%d"), INTERVAL 7 DAY);

-- 2. Subtract 12 hours from the current time
SELECT DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 12 HOUR);

-- 3. Subtract 60 minutes from the current date
SELECT DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 60 MINUTE);

-- 4. Subtract 1 year from the current date
SELECT DATE_SUB(DATE_FORMAT(CURRENT_DATE(), "%Y-%m-%d"), INTERVAL 1 YEAR);

-- 5. Subtract 1 month from the current date
SELECT DATE_SUB(DATE_FORMAT(CURRENT_DATE(), "%Y-%m-%d"), INTERVAL 1 MONTH);

DATE_ADD

The DATE_ADD function adds a specified time interval to a date. It can add years, months, days, hours, minutes, or seconds to a date or datetime field.

-- Function: DATE_ADD
-- 1. Add 7 days to the current date
SELECT DATE_ADD(DATE_FORMAT(CURRENT_DATE(), "%Y-%m-%d"), INTERVAL 7 DAY);

-- 2. Add 12 hours to the current time
SELECT DATE_ADD(CURRENT_TIMESTAMP(), INTERVAL 12 HOUR);

-- 3. Add 60 minutes to the current date
SELECT DATE_ADD(CURRENT_TIMESTAMP(), INTERVAL 60 MINUTE);

-- 4. Add 1 year to the current date
SELECT DATE_ADD(DATE_FORMAT(CURRENT_DATE(), "%Y-%m-%d"), INTERVAL 1 YEAR);

-- 5. Add 1 month to the current date
SELECT DATE_ADD(DATE_FORMAT(CURRENT_DATE(), "%Y-%m-%d"), INTERVAL 1 MONTH);

NOW

The NOW function returns the current date and time in the 'YYYY-MM-DD HH:MM:SS' format.

-- Function: NOW
-- Returns a value like: 2024-02-21 14:31:45
SELECT NOW() FROM employees;

CURDATE

The CURDATE function returns the current date in 'YYYY-MM-DD' format.

-- Function: CURDATE
SELECT CURDATE(); -- Returns: 2024-02-21

CURTIME

The CURTIME function returns the current time in 'HH:MM:SS' format.

-- Function: CURTIME
SELECT CURTIME(); -- Returns: 17:56:39

DATEDIFF

The DATEDIFF function returns the number of days between two dates. It calculates date1 - date2, using only the date part of datetime values.

-- Function: DATEDIFF(expr1, expr2)
SELECT DATEDIFF('2024-04-30 13:00:00', '2024-04-29 14:00:00');

TIMEDIFF

The TIMEDIFF function returns the difference between two time values, calculated as time1 - time2.

-- Function: TIMEDIFF(expr1, expr2)
-- Returns format: HH:MM:SS, both parameters must be valid datetime values
SELECT TIMEDIFF('2024-02-21 00:00:00', '2024-02-20 00:00:00');

TIMESTAMPDIFF

The TIMESTAMPDIFF function returns the difference between two datetime values, with the result expressed in the specified interval unit.

-- Function: TIMESTAMPDIFF
SELECT TIMESTAMPDIFF(SECOND, '2024-2-19', '2024-2-20');  -- 86400 seconds
SELECT TIMESTAMPDIFF(MINUTE, '2024-2-19', '2024-2-20');  -- 1440 minutes
SELECT TIMESTAMPDIFF(HOUR, '2024-2-19', '2024-2-20');    -- 24 hours
SELECT TIMESTAMPDIFF(DAY, '2024-1-19', '2024-2-20');     -- 32 days
SELECT TIMESTAMPDIFF(WEEK, '2024-1-19', '2024-2-20');    -- 4 weeks
SELECT TIMESTAMPDIFF(MONTH, '2024-1-19', '2024-2-20');   -- 1 month
SELECT TIMESTAMPDIFF(QUARTER, '2023-1-19', '2024-2-20'); -- 4 quarters
SELECT TIMESTAMPDIFF(YEAR, '2023-1-19', '2024-2-20');    -- 1 year

TO_DAYS

The TO_DAYS function converts a date to the number of days since year 0 (0000-00-00).

-- Function: TO_DAYS(date)
SELECT TO_DAYS(CURRENT_TIMESTAMP()) FROM employees;
-- Calculate the number of days from a datetime field to today
SELECT id, employee_name, TO_DAYS(CURRENT_DATE()) - TO_DAYS(creation_date) AS days_ago 
FROM employees;

STR_TO_DATE

The STR_TO_DATE function converts a string to a date/time value based on a specified format.

-- Function: STR_TO_DATE(str, format)
SELECT STR_TO_DATE('25,2,2024', '%d,%m,%Y'); -- 2024-02-25
SELECT STR_TO_DATE('20240222 113055', '%Y%m%d %h%i%s'); -- 2024-02-22 11:30:00

-- Create a date string
SET @dateString = '2024-02-24 15:05:30';
-- Convert the string to date type
SET @date = STR_TO_DATE(@dateString, '%Y-%m-%d %T');
-- Use the converted date for operations
-- Compare dates
SELECT * FROM employees WHERE creation_date > @date; 
-- Calculate date intervals
SELECT DATEDIFF(@date, creation_date) AS dateDiff FROM employees;

String Functions

UPPER

The UPPER function converts all characters in a string to uppercase.

-- Function: UPPER
SELECT id, UPPER(employee_name), password, IFNULL(department, 'No Department') 
FROM employees;

LOWER

The LOWER function converts all characters in a string to lowercase.

-- Function: LOWER
SELECT id, LOWER(employee_name), password, IFNULL(department, 'No Department') 
FROM employees;

CONCAT

The CONCAT function combines two or more strings into a single string.

-- Function: CONCAT
-- Note: If department or location is NULL, the result will be NULL
-- SELECT CONCAT(employee_name, password) AS login_info, CONCAT(department, location) AS office_location 
-- FROM employees
-- Solution: Use IFNULL to handle NULL values
SELECT CONCAT(employee_name, password) AS login_info, 
       CONCAT(IFNULL(department, ""), IFNULL(location, "")) AS office_location 
FROM employees;

CONCAT_WS

The CONCAT_WS function (Concatenate With Separator) is a special form of CONCAT that uses a separator between the strings.

-- Function: CONCAT_WS(separator, str1, str2, ...)
SELECT CONCAT_WS(':', employee_name, password) AS login_info, 
       CONCAT_WS("-", department, location) AS office_location 
FROM employees;

GROUP_CONCAT

The GROUP_CONCAT function concatenates values from a group into a single string, separated by a specified delimiter.

-- Function: GROUP_CONCAT
SELECT department, 
       GROUP_CONCAT(employee_name ORDER BY employee_name DESC SEPARATOR ' / ') AS team_members
FROM employees
WHERE department = 'Engineering';

REPEAT

The REPEAT function repeats a string a specified number of times.

-- Function: REPEAT
SELECT REPEAT(department, 3) AS repeated_department FROM employees;

SUBSTRING_INDEX

The SUBSTRING_INDEX function returns a substring from a string before a specified number of delimiter occurrences.

-- Function: SUBSTRING_INDEX(str, delim, count)
-- Positive count: from left to right
SELECT SUBSTRING_INDEX(email, ".", 1) FROM employees WHERE email = 'user@company.com';
-- Negative count: from right to left
SELECT SUBSTRING_INDEX(email, ".", -1) FROM employees WHERE email = 'user@company.com';

SUBSTRING

The SUBSTRING function extracts a substring from a string, starting at a specified position and with a specified length.

-- Function: SUBSTRING
-- Extract 2 characters from department name starting at position 1
-- Extract 3 characters from employee_name starting at position 3
SELECT SUBSTRING(department, 1, 2), SUBSTRING(employee_name, 3, 3) FROM employees;

Other Functions

CAST

The CAST function converts a value from one data type to another.

-- Function: CAST(value AS datatype)
-- 1. Convert to DATE
SELECT CAST('2024-02-21' AS DATE); -- Convert date string to date format
SELECT CAST(CURRENT_TIMESTAMP() AS DATE); -- Convert datetime to date

-- 2. Convert to DATETIME
SELECT CAST('2024-02-21' AS DATETIME); -- 2024-02-21 00:00:00

-- 3. Convert to TIME
SELECT CAST('21:25:10' AS TIME); -- 21:25:10
SELECT CAST('2024-04-27 14:06:10' AS TIME); -- 14:06:10

-- 4. Convert to CHAR
SELECT CAST(150 AS CHAR); 
SELECT CONCAT('Employee ID: ', CAST(437 AS CHAR));

-- 5. Convert to SIGNED (signed integer)
SELECT CAST('5.0' AS SIGNED); -- 5
SELECT (1 + CAST('3' AS SIGNED))/2; -- 2.0000
SELECT CAST(5-10 AS SIGNED); -- -5
SELECT CAST(6.4 AS SIGNED);  -- 6 (rounded)
SELECT CAST(6.5 AS SIGNED);  -- 7 (rounded)
SELECT CAST(-6.5 AS SIGNED); -- -7 (rounded)

-- 6. Convert to UNSIGNED (unsigned integer)
SELECT CAST('5.0' AS UNSIGNED); -- 5
SELECT CAST(6.4 AS UNSIGNED);   -- 6
SELECT CAST(-6.4 AS UNSIGNED);  -- 0
SELECT CAST(6.5 AS UNSIGNED);   -- 7
SELECT CAST(-6.5 AS UNSIGNED);  -- 0

-- 7. Convert to DECIMAL
SELECT CAST('9.0' AS DECIMAL); -- 9
-- DECIMAL(precision, scale)
-- DECIMAL(10,2) can store numbers with up to 8 integer digits and 2 decimal digits
SELECT CAST('9.5' AS DECIMAL(10,2)); -- 9.50
SELECT CAST('1234567890.123' AS DECIMAL(10,2)); -- 99999999.99
SELECT CAST('220.23211231' AS DECIMAL(10, 3));  -- 220.232
SELECT CAST(220.23211231 AS DECIMAL(10, 3));    -- 220.232

ROUND

The ROUND function rounds a number to a specified number of decimal places.

-- Function: ROUND(number, decimals)
-- decimals can be negative to round to the left of the decimal point
SELECT ROUND(1123.26723, 2);  -- 1123.27
SELECT ROUND(1123.26723, 1);  -- 1123.3
SELECT ROUND(1123.26723, 0);  -- 1123
SELECT ROUND(1123.26723, -1); -- 1120
SELECT ROUND(1123.26723, -2); -- 1100
SELECT ROUND(1123.26723);    -- 1123 (default is 0 decimals)

COALESCE

The COALESCE function returns the first non-NULL value in a list of expressions.

-- Function: COALESCE(expr1, expr2, ..., exprN)
-- If department is NULL, return 'No Department'
SELECT id, employee_name, password, COALESCE(department, 'No Department') AS department 
FROM employees;

-- If department is not NULL, return its value
-- If department is NULL but location is not NULL, return location
-- If both are NULL, return 'No Location'
SELECT id, employee_name, password, 
       COALESCE(department, location, 'No Location') AS department_or_location 
FROM employees;

CASE WHEN

The CASE WHEN function allows conditional logic in SQL queries, similar to a switch-case statement.

-- Function: CASE WHEN condition THEN result ELSE alternative END
-- Count employees by performance score ranges
SELECT 
    COUNT(CASE WHEN performance_score >= 0 AND performance_score <= 59 THEN 1 END) AS needs_improvement,
    COUNT(CASE WHEN performance_score >= 60 THEN 1 END) AS meets_expectations,
    AVG(CASE 
            WHEN performance_score >= 0 AND performance_score <= 59 THEN performance_score
            ELSE NULL
        END) AS avg_needs_improvement,
    AVG(CASE 
            WHEN performance_score >= 60 THEN performance_score
            ELSE NULL
        END) AS avg_meets_expectations
FROM employees;

-- Set salary multiplier for specific departments
SELECT id, employee_name,
    CASE 
        WHEN department IN ('Engineering', 'Research') THEN 1.2
        WHEN department IN ('Marketing', 'Sales') THEN 1.1
        ELSE 1.0
    END AS salary_multiplier
FROM employees;

IFNULL

The IFNULL function returns the first expression if it is not NULL; otherwise, it returns the second expression.

-- Function: IFNULL(expression, alternative)
SELECT id, employee_name, password, IFNULL(department, 'No Department') 
FROM employees;

LEAST

The LEAST function returns the smallest value from a list of expressions.

-- Function: LEAST(value1, value2, ...)
-- Cap performance scores at 100
SELECT LEAST(performance_score, 100) FROM employees;

GREATEST

The GREATEST function returns the largest value from a list of expressions.

-- Function: GREATEST(value1, value2, ...)
-- Set minimum performance score to 60
SELECT GREATEST(performance_score, 60) FROM employees;

REGEXP

The REGEXP function performs pattern matching using regular expressions.

-- Function: REGEXP pattern
-- Find employees whose names start with 'Jo'
SELECT * FROM employees WHERE employee_name REGEXP '^Jo';

-- Find employees whose IDs end with 01 or 02
SELECT * FROM employees WHERE employee_id REGEXP '01$|02$';

-- Find employees with a 3-character middle name (e.g., "J o h")
SELECT * FROM employees WHERE employee_name REGEXP 'J..h';

-- Find employees whose names contain "son"
SELECT * FROM employees WHERE employee_name REGEXP 'son';

-- Find employees whose names contain either 'a' or 'e'
SELECT * FROM employees WHERE employee_name REGEXP '[ae]';

-- Find employees whose names contain at least 2 'p' characters
SELECT * FROM employees WHERE employee_name REGEXP 'p{2}';

-- Find employees whose names contain 'p' 1 to 3 times
SELECT * FROM employees WHERE employee_name REGEXP 'p{1,3}';

Tags: sql database functions date functions String Functions data manipulation

Posted on Sat, 10 Oct 2026 16:16:48 +0000 by drisate

Freaks City

Essential SQL Functions for Data Manipulation