Part 4: Date/Time, Numeric & Control Flow

Date/Time Functions

BEGINNER

`NOW()`

Returns the current date and time as a single DATETIME value. Every row in the same statement gets the same value. Useful for recording when an order was created or when a task was started.

SELECT NOW() AS current_datetime;

current_datetime
2026-02-23 14:35:12

-- Record when an order was placed
INSERT INTO orders (customer_id, product_id, order_date)
VALUES (1042, 7, NOW());

`CURDATE()`

Returns today's date without the time portion. Handy for date-only comparisons like "find all employees hired today."

SELECT CURDATE() AS today;

today
2026-02-23

-- Find employees who started today
SELECT name, hire_date
FROM employees
WHERE DATE(hire_date) = CURDATE();

name	hire_date
Maria Santos	2026-02-23 07:15:00
Jose Reyes	2026-02-23 09:42:00

`CURTIME()`

Returns the current time without the date. Useful for checking shift schedules or whether the office is within business hours.

SELECT CURTIME() AS current_time_now;

current_time_now
14:35:12

`DATE(datetime)`

Strips the time portion from a DATETIME value, returning only the date. Commonly used in WHERE clauses to compare by date regardless of what time an event occurred.

SELECT
  order_id,
  order_date,
  DATE(order_date) AS date_only
FROM orders
WHERE order_id IN (501, 502, 503);

order_id	order_date	date_only
501	2026-02-22 08:14:33	2026-02-22
502	2026-02-22 11:47:05	2026-02-22
503	2026-02-23 06:30:00	2026-02-23

`TIME(datetime)`

Extracts only the time portion from a DATETIME. Useful for analyzing what time of day orders are placed.

SELECT
  order_id,
  order_date,
  TIME(order_date) AS order_time
FROM orders
WHERE order_id IN (501, 502);

order_id	order_date	order_time
501	2026-02-22 08:14:33	08:14:33
502	2026-02-22 11:47:05	11:47:05

`YEAR(date)`

Returns the 4-digit year from a date. Useful for annual reports or grouping orders by year.

SELECT
  YEAR(order_date) AS order_year,
  COUNT(*) AS total_orders
FROM orders
GROUP BY YEAR(order_date)
ORDER BY order_year;

order_year	total_orders
2024	14320
2025	15887
2026	2764

`MONTH(date)`

Returns the month number (1-12) from a date. Great for monthly summaries of order volume.

SELECT
  MONTH(order_date) AS month_num,
  COUNT(*) AS orders
FROM orders
WHERE YEAR(order_date) = 2026
GROUP BY MONTH(order_date)
ORDER BY month_num;

month_num	orders
1	1538
2	1226

`DAY(date)`

Returns the day of the month (1-31). Also available as DAYOFMONTH(). Handy for daily breakdowns.

SELECT
  DAY(created_at) AS day_of_month,
  COUNT(*) AS tasks_created
FROM tasks
WHERE MONTH(created_at) = 2
  AND YEAR(created_at) = 2026
GROUP BY DAY(created_at)
ORDER BY day_of_month
LIMIT 5;

day_of_month	tasks_created
1	67
2	72
3	55
4	61
5	68

`HOUR(time)`

Returns the hour (0-23) from a time or datetime value. Perfect for analyzing peak hours at the office.

SELECT
  HOUR(created_at) AS hour_of_day,
  COUNT(*) AS tasks
FROM tasks
WHERE DATE(created_at) = '2026-02-23'
GROUP BY HOUR(created_at)
ORDER BY hour_of_day
LIMIT 5;

hour_of_day	tasks
6	8
7	22
8	31
9	27
10	19

`MINUTE(time)`

Returns the minute (0-59) from a time or datetime value.

SELECT
  order_id,
  order_date,
  MINUTE(order_date) AS minute_part
FROM orders
WHERE order_id IN (501, 502);

order_id	order_date	minute_part
501	2026-02-22 08:14:33	14
502	2026-02-22 11:47:05	47

`SECOND(time)`

Returns the second (0-59) from a time or datetime value.

SELECT
  order_id,
  order_date,
  SECOND(order_date) AS second_part
FROM orders
WHERE order_id IN (501, 502);

order_id	order_date	second_part
501	2026-02-22 08:14:33	33
502	2026-02-22 11:47:05	5

`DATE_ADD(date, INTERVAL n unit)`

Adds a time interval to a date. The unit can be DAY, MONTH, YEAR, HOUR, MINUTE, SECOND, and more. This is essential for calculating deadlines, expiry dates, and follow-up schedules.

Adding Days

-- When does the product warranty expire? (warranty = 90 days)
SELECT
  product_name,
  purchase_date,
  DATE_ADD(purchase_date, INTERVAL 90 DAY) AS warranty_end
FROM products
LIMIT 3;

product_name	purchase_date	warranty_end
Office Chair	2026-01-10	2026-04-10
Standing Desk	2026-01-25	2026-04-25
Monitor Arm	2026-02-05	2026-05-06

Adding Months

-- Schedule 6-month performance review for employees
SELECT
  name,
  last_review,
  DATE_ADD(last_review, INTERVAL 6 MONTH) AS next_review
FROM employees
WHERE department = 'Engineering'
LIMIT 2;

name	last_review	next_review
Ana Cruz	2025-12-15	2026-06-15
Pedro Lim	2026-01-20	2026-07-20

Adding Years

-- Equipment lease expiry (2-year lease)
SELECT
  product_name,
  lease_start,
  DATE_ADD(lease_start, INTERVAL 2 YEAR) AS lease_ends
FROM products
LIMIT 2;

product_name	lease_start	lease_ends
Dell Server R740	2024-06-15	2026-06-15
HP LaserJet Pro	2025-01-10	2027-01-10

Adding Hours

-- Urgent task deadline (2 hours from creation)
SELECT
  task_id,
  created_at,
  DATE_ADD(created_at, INTERVAL 2 HOUR) AS deadline
FROM tasks
WHERE priority = 'URGENT'
LIMIT 2;

task_id	created_at	deadline
8801	2026-02-23 09:15:00	2026-02-23 11:15:00
8805	2026-02-23 10:42:00	2026-02-23 12:42:00

`DATE_SUB(date, INTERVAL n unit)`

Subtracts a time interval from a date. Works exactly like DATE_ADD but in reverse. Great for looking at historical data windows.

-- Find all orders from the last 7 days
SELECT
  order_id,
  customer_name,
  order_date
FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
ORDER BY order_date DESC
LIMIT 4;

order_id	customer_name	order_date
8810	Maria Santos	2026-02-23
8807	Jose Reyes	2026-02-22
8795	Ana Cruz	2026-02-20
8788	Luis Garcia	2026-02-18

`DATEDIFF(date1, date2)`

Returns the number of days between two dates (date1 minus date2). Always returns an integer. Extremely useful for tracking delivery time and order fulfillment speed.

-- How many days between order and shipment?
SELECT
  order_id,
  product_name,
  order_date,
  ship_date,
  DATEDIFF(ship_date, order_date) AS days_to_ship
FROM orders
WHERE ship_date IS NOT NULL
LIMIT 4;

order_id	product_name	order_date	ship_date	days_to_ship
8750	Laptop Stand	2026-02-18	2026-02-18	0
8751	Ergonomic Keyboard	2026-02-18	2026-02-19	1
8752	Custom Server	2026-02-17	2026-02-22	5
8753	Webcam HD	2026-02-19	2026-02-20	1

`TIMEDIFF(time1, time2)`

Returns the difference between two time or datetime values as a TIME value (HH:MM:SS). Unlike DATEDIFF which counts days, this gives you the exact time difference.

SELECT
  task_id,
  created_at,
  completed_at,
  TIMEDIFF(completed_at, created_at) AS duration
FROM tasks
LIMIT 3;

task_id	created_at	completed_at	duration
501	2026-02-22 08:14:33	2026-02-22 08:45:10	00:30:37
502	2026-02-22 11:47:05	2026-02-22 12:10:22	00:23:17
503	2026-02-23 06:30:00	2026-02-23 07:15:44	00:45:44

`TIMESTAMPDIFF(unit, start, end)`

Returns the integer difference between two datetimes in the specified unit (YEAR, MONTH, DAY, HOUR, MINUTE, SECOND). Unlike TIMEDIFF, you choose what unit you want back.

-- Employee tenure in years, months since hire, and task completion in hours
SELECT
  name,
  birth_date,
  TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age_years,
  hire_date,
  TIMESTAMPDIFF(MONTH, hire_date, CURDATE()) AS months_employed,
  task_created,
  task_completed,
  TIMESTAMPDIFF(HOUR, task_created, task_completed) AS completion_hours
FROM employee_tasks_view
LIMIT 3;

name	birth_date	age_years	hire_date	months_employed	task_created	task_completed	completion_hours
Maria Santos	1958-07-14	67	2026-02-20	0	2026-02-20 08:00:00	2026-02-20 10:15:00	2
Jose Reyes	1990-03-22	35	2025-11-10	3	2025-11-10 14:30:00	2025-11-11 09:00:00	18
Ana Cruz	2001-12-01	24	2026-01-05	1	2026-01-05 07:45:00	2026-01-05 08:30:00	0

`DATE_FORMAT(date, format)`

Formats a date/datetime value into a string using format specifiers. This is the most flexible way to display dates in reports. Common specifiers: %Y (4-digit year), %m (month 01-12), %d (day 01-31), %M (month name), %W (weekday name), %h (hour 12-hr), %H (hour 24-hr), %i (minute), %s (second), %p (AM/PM).

SELECT
  order_id,
  order_date,
  DATE_FORMAT(order_date, '%Y-%m-%d') AS iso_format,
  DATE_FORMAT(order_date, '%M %d, %Y') AS long_format,
  DATE_FORMAT(order_date, '%W') AS day_name,
  DATE_FORMAT(order_date, '%h:%i %p') AS time_12hr,
  DATE_FORMAT(order_date, '%d/%m/%Y %H:%i') AS euro_format
FROM orders
LIMIT 2;

order_id	order_date	iso_format	long_format	day_name	time_12hr	euro_format
8801	2026-02-23 09:15:00	2026-02-23	February 23, 2026	Monday	09:15 AM	23/02/2026 09:15
8802	2026-02-23 14:42:30	2026-02-23	February 23, 2026	Monday	02:42 PM	23/02/2026 14:42

`STR_TO_DATE(string, format)`

The reverse of DATE_FORMAT -- it parses a string into a date using the same format specifiers. Essential when importing data from external systems that use non-standard date formats.

-- Parse dates from an imported CSV
SELECT
  STR_TO_DATE('23-Feb-2026', '%d-%b-%Y') AS parsed_date1,
  STR_TO_DATE('02/23/2026 09:30 AM', '%m/%d/%Y %h:%i %p') AS parsed_date2,
  STR_TO_DATE('2026.02.23', '%Y.%m.%d') AS parsed_date3;

parsed_date1	parsed_date2	parsed_date3
2026-02-23	2026-02-23 09:30:00	2026-02-23

`LAST_DAY(date)`

Returns the last day of the month for the given date. Very handy for month-end reports and billing cycle calculations. It correctly handles February in leap years.

SELECT
  LAST_DAY('2026-02-15') AS feb_end,
  LAST_DAY('2026-04-10') AS apr_end,
  LAST_DAY('2024-02-01') AS leap_feb_end;

feb_end	apr_end	leap_feb_end
2026-02-28	2026-04-30	2024-02-29

`DAYNAME(date)`

Returns the name of the day of the week (Monday, Tuesday, etc.). Useful for analyzing which days the business is busiest.

SELECT
  DAYNAME(order_date) AS day_name,
  COUNT(*) AS total_orders
FROM orders
WHERE order_date BETWEEN '2026-02-01' AND '2026-02-23'
GROUP BY DAYNAME(order_date),
         DAYOFWEEK(order_date)
ORDER BY DAYOFWEEK(order_date);

day_name	total_orders
Sunday	42
Monday	198
Tuesday	187
Wednesday	175
Thursday	182
Friday	168
Saturday	74

`DAYOFWEEK(date)`

Returns the day-of-week index where 1 = Sunday, 2 = Monday, ..., 7 = Saturday. Useful for numeric sorting of days and filtering weekends.

-- Find orders placed on weekends
SELECT
  order_id,
  order_date,
  DAYNAME(order_date) AS day_name,
  DAYOFWEEK(order_date) AS dow_number
FROM orders
WHERE DAYOFWEEK(order_date) IN (1, 7) -- Sunday=1, Saturday=7
LIMIT 3;

order_id	order_date	day_name	dow_number
8760	2026-02-21	Saturday	7
8765	2026-02-22	Sunday	1
8768	2026-02-22	Sunday	1

`DAYOFYEAR(date)`

Returns the day of the year (1-366). Useful for sequential numbering in reports and tracking progress through the year.

SELECT
  DAYOFYEAR('2026-01-01') AS jan1,
  DAYOFYEAR('2026-02-23') AS today,
  DAYOFYEAR('2026-12-31') AS dec31;

jan1	today	dec31
1	54	365

`WEEK(date)`

Returns the week number (0-53) for the given date. Useful for weekly reports. The default mode counts Sunday as the first day of the week.

SELECT
  WEEK('2026-02-23') AS week_num,
  WEEK('2026-01-01') AS jan1_week,
  WEEK('2026-12-31') AS dec31_week;

week_num	jan1_week	dec31_week
8	0	52

-- Weekly order volume
SELECT
  WEEK(order_date) AS week_num,
  MIN(order_date) AS week_start,
  COUNT(*) AS orders
FROM orders
WHERE YEAR(order_date) = 2026
GROUP BY WEEK(order_date)
ORDER BY week_num
LIMIT 4;

week_num	week_start	orders
0	2026-01-01	185
1	2026-01-04	342
2	2026-01-11	358
3	2026-01-18	371

`WEEKDAY(date)`

Returns the weekday index where 0 = Monday, 1 = Tuesday, ..., 6 = Sunday. Note this is different from DAYOFWEEK() which starts with 1 = Sunday.

SELECT
  '2026-02-23' AS the_date,
  DAYNAME('2026-02-23') AS day_name,
  WEEKDAY('2026-02-23') AS weekday_idx,
  DAYOFWEEK('2026-02-23') AS dayofweek_idx;

the_date	day_name	weekday_idx	dayofweek_idx
2026-02-23	Monday	0	2

Note: WEEKDAY(Monday) = 0, but DAYOFWEEK(Monday) = 2. Keep this difference in mind when writing queries.

`EXTRACT(unit FROM date)`

Extracts a specific part from a date using the EXTRACT keyword. This is the SQL standard way to pull out date parts. It works with YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, and combined units like YEAR_MONTH.

SELECT
  order_date,
  EXTRACT(YEAR FROM order_date) AS yr,
  EXTRACT(MONTH FROM order_date) AS mo,
  EXTRACT(DAY FROM order_date) AS dy,
  EXTRACT(HOUR FROM order_date) AS hr,
  EXTRACT(YEAR_MONTH FROM order_date) AS yr_mo
FROM orders
WHERE order_id = 8801;

order_date	yr	mo	dy	hr	yr_mo
2026-02-23 09:15:00	2026	2	23	9	202602

`UNIX_TIMESTAMP(date)`

Converts a date or datetime to a Unix timestamp (seconds since 1970-01-01 00:00:00 UTC). Useful for interfacing with systems that store timestamps as integers, like some external APIs.

SELECT
  UNIX_TIMESTAMP() AS current_ts,
  UNIX_TIMESTAMP('2026-02-23 09:15:00') AS specific_ts,
  UNIX_TIMESTAMP('1970-01-01 00:00:00') AS epoch_zero;

current_ts	specific_ts	epoch_zero
1771920912	1771916100	0

`FROM_UNIXTIME(timestamp)`

Converts a Unix timestamp back to a readable DATETIME. The reverse of UNIX_TIMESTAMP(). Useful when reading timestamps from external APIs or third-party integrations.

SELECT
  FROM_UNIXTIME(1771916100) AS readable_datetime,
  FROM_UNIXTIME(1771916100, '%M %d, %Y at %h:%i %p') AS formatted;

readable_datetime	formatted
2026-02-23 09:15:00	February 23, 2026 at 09:15 AM

Numeric/Math Functions

BEGINNER

`ROUND(number, decimals)`

Rounds a number to the specified number of decimal places. If decimals is omitted, it rounds to 0 decimal places (nearest integer). Uses "round half away from zero" (banker's rounding is NOT the default in MySQL).

-- Rounding product prices for display
SELECT
  product_name,
  unit_price,
  ROUND(unit_price) AS rounded_0,
  ROUND(unit_price, 1) AS rounded_1,
  ROUND(unit_price, 2) AS rounded_2
FROM products
LIMIT 4;

product_name	unit_price	rounded_0	rounded_1	rounded_2
Wireless Mouse	126.847	127	126.8	126.85
USB Hub	1.2345	1	1.2	1.23
Monitor Stand	14.5500	15	14.6	14.55
Cable Kit	7.8912	8	7.9	7.89

`CEIL() / CEILING()`

Rounds a number UP to the nearest integer (toward positive infinity). CEIL and CEILING are identical. Useful when you need "at least this many" -- like calculating how many shipping boxes to order.

-- How many boxes to order? (each box holds 100 items)
SELECT
  product_name,
  monthly_demand,
  monthly_demand / 100.0 AS exact_boxes,
  CEIL(monthly_demand / 100.0) AS boxes_to_order
FROM product_demand
LIMIT 3;

product_name	monthly_demand	exact_boxes	boxes_to_order
Widget A	1250	12.5000	13
Widget B	890	8.9000	9
Widget C	401	4.0100	5

`FLOOR()`

Rounds a number DOWN to the nearest integer (toward negative infinity). Useful when you want whole completed units only -- like "how many full pallets can we ship?"

-- How many full pallets of 20 can we ship with available inventory?
SELECT
  product_name,
  stock_qty,
  FLOOR(stock_qty / 20) AS full_pallets,
  stock_qty MOD 20 AS remaining
FROM inventory
LIMIT 3;

product_name	stock_qty	full_pallets	remaining
Laptop Stand	87	4	7
Desk Lamp	43	2	3
Cable Tray	19	0	19

`ABS()`

Returns the absolute (non-negative) value of a number. Useful for calculating how far a value deviates from a target regardless of direction.

-- How far are actual sales from the forecast?
SELECT
  report_id,
  product_name,
  actual_sales,
  forecast,
  actual_sales - forecast AS deviation,
  ABS(actual_sales - forecast) AS abs_deviation
FROM sales_reports
LIMIT 4;

report_id	product_name	actual_sales	forecast	deviation	abs_deviation
1001	Widget A	102.3	100.0	2.3	2.3
1002	Widget A	97.1	100.0	-2.9	2.9
1003	Widget B	1.18	1.20	-0.02	0.02
1004	Widget B	1.25	1.20	0.05	0.05

`MOD(n, m)`

Returns the remainder of n divided by m (modulo). Also available as the % operator. Handy for alternating row colors, cycling through assignments, or checking even/odd.

-- Assign support staff in round-robin by task_id
SELECT
  task_id,
  MOD(task_id, 3) AS staff_slot,
  CASE MOD(task_id, 3)
    WHEN 0 THEN 'Alice'
    WHEN 1 THEN 'Bob'
    WHEN 2 THEN 'Carol'
  END AS assigned_to
FROM tasks
LIMIT 5;

task_id	staff_slot	assigned_to
8801	2	Carol
8802	0	Alice
8803	1	Bob
8804	2	Carol
8805	0	Alice

`POWER(base, exp) / POW()`

Raises a number to a given power. POWER and POW are identical. Used in statistical formulas, compound interest calculations, and analytics.

-- Calculate cost per square meter: price / area^2
SELECT
  name,
  salary,
  years_employed,
  ROUND(salary * POW(1.03, years_employed), 1) AS projected_salary
FROM employees
LIMIT 3;

name	salary	years_employed	projected_salary
Maria Santos	68500	5	79420.8
Jose Reyes	82000	3	89578.9
Ana Cruz	55000	1	56650.0

`SQRT()`

Returns the square root of a number. Used in statistical calculations like standard deviation in performance analysis.

-- Standard deviation components for sales metrics
SELECT
  product_name,
  variance_val,
  SQRT(variance_val) AS std_deviation,
  ROUND(SQRT(variance_val), 3) AS sd_rounded
FROM sales_stats
LIMIT 3;

product_name	variance_val	std_deviation	sd_rounded
Widget A	9.61	3.1	3.100
Widget B	0.0025	0.05	0.050
Widget C	0.49	0.7	0.700

`RAND()`

Returns a random floating-point number between 0 (inclusive) and 1 (exclusive). Pass an integer seed for reproducible results. Practical use: selecting random records for audit.

-- Select 3 random orders for quality audit
SELECT
  order_id,
  customer_name,
  product_name,
  total
FROM orders
WHERE order_date = CURDATE()
ORDER BY RAND()
LIMIT 3;

order_id	customer_name	product_name	total
44217	Pedro Lim	Desk Lamp	18.50
44203	Ana Cruz	Cable Kit	6.80
44251	Maria Santos	USB Hub	4.20

Performance Warning: ORDER BY RAND() on large tables is slow because it generates a random value for every row. For big tables, consider using a subquery with LIMIT on the primary key.

`TRUNCATE(number, decimals)`

Chops off digits after the specified decimal place WITHOUT rounding. Unlike ROUND, it always drops towards zero.

-- Compare TRUNCATE vs ROUND
SELECT
  126.847 AS original,
  ROUND(126.847, 1) AS rounded,
  TRUNCATE(126.847, 1) AS truncated
UNION ALL
SELECT 126.851, ROUND(126.851, 1), TRUNCATE(126.851, 1)
UNION ALL
SELECT -3.678, ROUND(-3.678, 1), TRUNCATE(-3.678, 1);

original	rounded	truncated
126.847	126.8	126.8
126.851	126.9	126.8
-3.678	-3.7	-3.6

`GREATEST(val1, val2, ...)`

Returns the largest value from a list of arguments. Useful for finding the highest score among multiple evaluations or picking the most recent of several dates.

-- Find the highest of three quarterly revenue figures
SELECT
  project_id,
  project_name,
  q1_revenue,
  q2_revenue,
  q3_revenue,
  GREATEST(q1_revenue, q2_revenue, q3_revenue) AS highest
FROM projects
LIMIT 3;

project_id	project_name	q1_revenue	q2_revenue	q3_revenue	highest
501	Alpha	125.3	127.1	126.0	127.1
502	Beta	4.1	4.3	4.2	4.3
503	Gamma	140.0	139.5	141.2	141.2

`LEAST(val1, val2, ...)`

Returns the smallest value from a list of arguments. The counterpart of GREATEST.

SELECT
  project_id,
  project_name,
  q1_revenue,
  q2_revenue,
  q3_revenue,
  LEAST(q1_revenue, q2_revenue, q3_revenue) AS lowest,
  GREATEST(q1_revenue, q2_revenue, q3_revenue) -
    LEAST(q1_revenue, q2_revenue, q3_revenue) AS range_spread
FROM projects
LIMIT 3;

project_id	project_name	q1_revenue	q2_revenue	q3_revenue	lowest	range_spread
501	Alpha	125.3	127.1	126.0	125.3	1.8
502	Beta	4.1	4.3	4.2	4.1	0.2
503	Gamma	140.0	139.5	141.2	139.5	1.7

Rounding Functions Comparison

This side-by-side comparison shows how ROUND, CEIL, FLOOR, and TRUNCATE behave on the same input values. Understanding these differences is critical for reporting accurate financial figures.

Input Value	ROUND(x)	CEIL(x)	FLOOR(x)	TRUNCATE(x, 0)
3.7	4	4	3	3
3.3	3	4	3	3
3.5	4	4	3	3
-2.5	-3	-2	-3	-2
-2.3	-2	-2	-3	-2
-2.7	-3	-2	-3	-2
0.9	1	1	0	0
-0.1	0	0	-1	0

Key Insight: ROUND goes to nearest integer. CEIL always goes UP (toward +infinity). FLOOR always goes DOWN (toward -infinity). TRUNCATE always goes toward ZERO (drops the decimal). Notice how CEIL and FLOOR differ from TRUNCATE on negative numbers!

Control Flow

BEGINNER

`IF(condition, true_val, false_val)`

An inline if-else expression. If the condition is true, returns the second argument; otherwise returns the third. Think of it like a ternary operator. Simple and fast for two-way branching.

-- Flag orders that exceeded budget
SELECT
  employee_name,
  expense_item,
  amount,
  budget_low,
  budget_high,
  IF(amount BETWEEN budget_low AND budget_high,
     'Within Budget', 'OVER BUDGET') AS flag
FROM expenses
LIMIT 5;

employee_name	expense_item	amount	budget_low	budget_high	flag
Maria Santos	Travel	126.8	70	100	OVER BUDGET
Maria Santos	Supplies	13.2	12.0	16.0	Within Budget
Jose Reyes	Software	11.5	4.5	11.0	OVER BUDGET
Ana Cruz	Office Meals	4.2	3.5	5.0	Within Budget
Pedro Lim	Shipping	0.9	0.7	1.3	Within Budget

`IFNULL(value, default)`

Returns the first argument if it is NOT NULL; otherwise returns the second argument. A shorthand for handling missing data -- very common when some tasks are pending or some employee fields are optional.

-- Show 'Pending' for tasks not yet completed
SELECT
  task_id,
  task_name,
  IFNULL(status, 'Pending') AS display_status,
  IFNULL(assigned_to, 'Unassigned') AS assignee
FROM tasks
LIMIT 4;

task_id	task_name	display_status	assignee
8801	Update Homepage	Done	Alice
8802	Fix Login Bug	In Progress	Bob
8803	Deploy v2.1	Pending	Unassigned
8804	Write Tests	Pending	Unassigned

`NULLIF(val1, val2)`

Returns NULL if val1 equals val2; otherwise returns val1. The classic use case is preventing division by zero: NULLIF(denominator, 0) turns a zero into NULL so division returns NULL instead of an error.

-- Calculate conversion rate without division-by-zero errors
SELECT
  campaign_name,
  total_visits,
  signups,
  ROUND(
    signups / NULLIF(total_visits, 0) * 100, 1
  ) AS conversion_pct
FROM marketing_stats
LIMIT 4;

campaign_name	total_visits	signups	conversion_pct
Spring Sale	450	32	7.1
Summer Promo	120	45	37.5
Fall Launch	200	8	4.0
Winter Draft	0	0	NULL

Without NULLIF: The Winter Draft row would cause a "Division by zero" error. With NULLIF(0, 0), the denominator becomes NULL, and any arithmetic with NULL returns NULL safely.

`COALESCE(val1, val2, ...)`

Returns the first non-NULL value in the list. More flexible than IFNULL because it accepts any number of arguments. Walk through them left to right and return the first one that is not NULL.

-- Use best available contact: mobile, then home, then work, then 'No Phone'
SELECT
  name,
  mobile_phone,
  home_phone,
  work_phone,
  COALESCE(mobile_phone, home_phone, work_phone, 'No Phone') AS best_contact
FROM employees
LIMIT 4;

name	mobile_phone	home_phone	work_phone	best_contact
Maria Santos	555-123-4567	555-812-3456	NULL	555-123-4567
Jose Reyes	NULL	555-823-4567	555-855-0001	555-823-4567
Ana Cruz	NULL	NULL	555-877-1234	555-877-1234
Pedro Lim	NULL	NULL	NULL	No Phone

`CASE WHEN ... THEN ... ELSE ... END`

The most powerful control flow expression in SQL. Evaluates conditions top-to-bottom and returns the value for the first true condition. If none match, returns the ELSE value (or NULL if no ELSE). Think of it as a multi-way IF or a switch statement.

How CASE Evaluates (Top to Bottom)

WHEN condition_1 → TRUE? → Return result_1

↓ FALSE

WHEN condition_2 → TRUE? → Return result_2

↓ FALSE

WHEN condition_3 → TRUE? → Return result_3

↓ FALSE

ELSE → Return default_value

Stops at the first match. Later conditions are never checked once a match is found.

-- Grade order fulfillment time
SELECT
  order_id,
  product_name,
  fulfillment_hours,
  CASE
    WHEN fulfillment_hours <= 1  THEN 'Excellent'
    WHEN fulfillment_hours <= 4  THEN 'Acceptable'
    WHEN fulfillment_hours <= 12 THEN 'Delayed'
    WHEN fulfillment_hours <= 24 THEN 'Late'
    ELSE 'Critical Delay'
  END AS fulfillment_grade
FROM orders
WHERE fulfillment_hours IS NOT NULL
LIMIT 6;

order_id	product_name	fulfillment_hours	fulfillment_grade
8801	Laptop Stand	0.5	Excellent
8802	Wireless Mouse	1.2	Acceptable
8803	Ergonomic Keyboard	3.8	Acceptable
8804	Custom Server	18.0	Late
8805	Standing Desk	6.5	Delayed
8806	Office Renovation Kit	72.0	Critical Delay

CASE with Budget Interpretation

-- Interpret project budget utilization
SELECT
  project_name,
  budget_used_pct,
  CASE
    WHEN budget_used_pct < 50  THEN 'Under-utilized'
    WHEN budget_used_pct <= 85 THEN 'On Track'
    WHEN budget_used_pct <= 100 THEN 'Near Limit'
    ELSE 'Over Budget'
  END AS interpretation
FROM projects
LIMIT 5;

project_name	budget_used_pct	interpretation
Alpha	142	Over Budget
Beta	88	Near Limit
Gamma	110	Over Budget
Delta	65	On Track
Epsilon	97	Near Limit

`CASE with GROUP BY` — Pivoting Rows to Columns

One of the most practical uses of CASE is pivoting data: turning row-level categories into separate columns. This technique uses CASE inside aggregate functions (SUM, COUNT) combined with GROUP BY to transform vertical data into a horizontal, spreadsheet-like layout.

-- Pivot: Count tasks by status for each department
SELECT
  department,
  COUNT(*) AS total,
  SUM(CASE WHEN status = 'Pending'     THEN 1 ELSE 0 END) AS pending,
  SUM(CASE WHEN status = 'In Progress' THEN 1 ELSE 0 END) AS in_progress,
  SUM(CASE WHEN status = 'Completed'   THEN 1 ELSE 0 END) AS completed,
  SUM(CASE WHEN status = 'Approved'    THEN 1 ELSE 0 END) AS approved
FROM tasks
WHERE created_at >= CURDATE()
GROUP BY department;

department	total	pending	in_progress	completed	approved
Engineering	45	5	8	12	20
Marketing	62	10	15	17	20
Operations	18	6	7	3	2
Sales	24	3	4	8	9

Pivot: Monthly Order Volume by Category

-- Turn months into columns for a year-over-month view
SELECT
  product_category,
  SUM(CASE WHEN MONTH(order_date) = 1  THEN 1 ELSE 0 END) AS Jan,
  SUM(CASE WHEN MONTH(order_date) = 2  THEN 1 ELSE 0 END) AS Feb,
  SUM(CASE WHEN MONTH(order_date) = 3  THEN 1 ELSE 0 END) AS Mar,
  SUM(CASE WHEN MONTH(order_date) = 4  THEN 1 ELSE 0 END) AS Apr
FROM orders
WHERE YEAR(order_date) = 2026
GROUP BY product_category;

product_category	Jan	Feb
Electronics	412	378
Furniture	589	524
Office Supplies	145	132
Software	203	192

Pivot Pattern: The trick is wrapping CASE inside an aggregate function (SUM or COUNT). Each CASE acts as a filter that only counts rows matching a specific category. GROUP BY then collapses rows, giving you one row per group with each category in its own column.

Part 4: Date/Time, Numeric & Control Flow

Table of Contents

NOW()

CURDATE()

CURTIME()

DATE(datetime)

TIME(datetime)

YEAR(date)

MONTH(date)

DAY(date)

HOUR(time)

MINUTE(time)

SECOND(time)

DATE_ADD(date, INTERVAL n unit)

Adding Days

Adding Months

Adding Years

Adding Hours

DATE_SUB(date, INTERVAL n unit)

DATEDIFF(date1, date2)

TIMEDIFF(time1, time2)

TIMESTAMPDIFF(unit, start, end)

DATE_FORMAT(date, format)

STR_TO_DATE(string, format)

LAST_DAY(date)

DAYNAME(date)

DAYOFWEEK(date)

DAYOFYEAR(date)

WEEK(date)

WEEKDAY(date)

EXTRACT(unit FROM date)

UNIX_TIMESTAMP(date)

FROM_UNIXTIME(timestamp)

ROUND(number, decimals)

CEIL() / CEILING()

FLOOR()

ABS()

MOD(n, m)

POWER(base, exp) / POW()

SQRT()

RAND()

TRUNCATE(number, decimals)

GREATEST(val1, val2, ...)

LEAST(val1, val2, ...)

Rounding Functions Comparison

IF(condition, true_val, false_val)

IFNULL(value, default)

NULLIF(val1, val2)

COALESCE(val1, val2, ...)

CASE WHEN ... THEN ... ELSE ... END

CASE with Budget Interpretation

CASE with GROUP BY — Pivoting Rows to Columns

Pivot: Monthly Order Volume by Category

`NOW()`

`CURDATE()`

`CURTIME()`

`DATE(datetime)`

`TIME(datetime)`

`YEAR(date)`

`MONTH(date)`

`DAY(date)`

`HOUR(time)`

`MINUTE(time)`

`SECOND(time)`

`DATE_ADD(date, INTERVAL n unit)`

`DATE_SUB(date, INTERVAL n unit)`

`DATEDIFF(date1, date2)`

`TIMEDIFF(time1, time2)`

`TIMESTAMPDIFF(unit, start, end)`

`DATE_FORMAT(date, format)`

`STR_TO_DATE(string, format)`

`LAST_DAY(date)`

`DAYNAME(date)`

`DAYOFWEEK(date)`

`DAYOFYEAR(date)`

`WEEK(date)`

`WEEKDAY(date)`

`EXTRACT(unit FROM date)`

`UNIX_TIMESTAMP(date)`

`FROM_UNIXTIME(timestamp)`

`ROUND(number, decimals)`

`CEIL() / CEILING()`

`FLOOR()`

`ABS()`

`MOD(n, m)`

`POWER(base, exp) / POW()`

`SQRT()`

`RAND()`

`TRUNCATE(number, decimals)`

`GREATEST(val1, val2, ...)`

`LEAST(val1, val2, ...)`

`IF(condition, true_val, false_val)`

`IFNULL(value, default)`

`NULLIF(val1, val2)`

`COALESCE(val1, val2, ...)`

`CASE WHEN ... THEN ... ELSE ... END`

`CASE with GROUP BY` — Pivoting Rows to Columns