DATEDIFF() in SQL
Introduction
DATEDIFF() in MySQL calculates the number of calendar days between two date values. It is useful for measuring days since registration, days remaining before expiration, delivery duration, project length, payment delay, defect resolution time, and many other date-based metrics.
SELECT DATEDIFF(
'2026-09-26',
'2026-09-20'
) AS day_difference;
-- 6
The basic mental model is first date minus second date. The function is simple, but correct interpretation depends on argument order, date versus time semantics, inclusive counting, business calendars, NULL policy, and the database product. MySQL and SQL Server both use the name DATEDIFF, yet their syntax and detailed behavior differ.
Basic Syntax
DATEDIFF(date1, date2)
MySQL converts compatible expressions to temporal values, uses their date portions, and returns an integer number of days. The calculation is date1 - date2.
SELECT DATEDIFF(
'2026-10-10',
'2026-10-01'
) AS elapsed_dates;
-- 9
The result describes distance between calendar dates. It does not automatically mean billable days, working days, nights charged, or complete 24-hour periods. Those interpretations belong to the business rule.
Argument Order Matters
Reversing the arguments reverses the sign.
SELECT DATEDIFF('2026-09-26', '2026-09-20');
-- 6
SELECT DATEDIFF('2026-09-20', '2026-09-26');
-- -6
For days since an event, place the current or ending date first. For days until a future event, place the future date first. Naming expressions as start and end before writing the function prevents many sign defects.
Positive, Negative, and Zero Results
| Result | Meaning | Example interpretation |
|---|---|---|
| Positive | First date is later | Days since a past event |
| Zero | Both values have the same date | Occurs or occurred today |
| Negative | First date is earlier | Arguments may be reversed, or event is future |
A negative value is not inherently an error. It can deliberately represent an early payment or a date that has not yet arrived. Define the sign convention in reports and APIs so consumers do not have to guess.
Same-Date Behavior
SELECT DATEDIFF('2026-09-26', '2026-09-26');
-- 0
Zero means no calendar-date boundary separates the values. If two DATETIME values occur on the same date but many hours apart, MySQL DATEDIFF() still returns zero because it is not an elapsed-time function.
Using DATEDIFF() with CURRENT_DATE
SELECT DATEDIFF(
CURRENT_DATE,
'2026-09-01'
) AS days_since_date;
This pattern measures calendar days from a stored or literal date through today. Reports that must be reproducible should bind an explicit as-of date instead of relying on the clock at execution time.
Days Since Registration
SELECT username,
registered_date,
DATEDIFF(
CURRENT_DATE,
registered_date
) AS days_registered
FROM users;
The result is useful for onboarding analysis and account cohorts. If registration is stored as a timestamp, DATEDIFF counts date boundaries, not exact elapsed 24-hour blocks since account creation.
Days Since an Order
SELECT order_id,
order_date,
DATEDIFF(CURRENT_DATE, order_date) AS days_since_order
FROM orders;
Customer service can use this display metric to understand order age. Fulfillment escalation may require working days, carrier calendars, or hours since a status transition instead, so do not treat the display value as a complete SLA calculation.
Days Until an Event
Put the future event first and the current date second.
SELECT event_name,
event_date,
DATEDIFF(event_date, CURRENT_DATE) AS days_remaining
FROM events;
A positive value means the date is ahead, zero means today, and a negative value means the date has passed. This makes one calculated column suitable for countdowns and overdue classification.
Days Until Subscription Expiration
SELECT subscription_id,
expiration_date,
DATEDIFF(expiration_date, CURRENT_DATE) AS days_remaining
FROM subscriptions;
Whether zero means active through today or already expired depends on how expiration_date is modeled. An exclusive expiration boundary usually avoids ambiguity, but user-facing wording must match the stored policy.
Finding Expirations Soon
A direct expression is easy to read for a small result set.
SELECT subscription_id, expiration_date
FROM subscriptions
WHERE DATEDIFF(expiration_date, CURRENT_DATE)
BETWEEN 0 AND 7;
Because the function is applied to each row's expiration date, a large table may benefit from a direct range predicate instead.
SELECT subscription_id, expiration_date
FROM subscriptions
WHERE expiration_date >= CURRENT_DATE
AND expiration_date < DATE_ADD(CURRENT_DATE, INTERVAL 8 DAY);
The correct upper boundary depends on whether the rule includes the date exactly seven days ahead and whether the column is DATE or DATETIME.
Difference Between Two Columns
SELECT order_id,
order_date,
delivery_date,
DATEDIFF(delivery_date, order_date) AS delivery_days
FROM orders;
Both values come from the same row, so the result can describe each order independently. NULL delivery dates naturally produce NULL and distinguish open orders from completed ones.
Project and Task Duration
SELECT project_name,
DATEDIFF(end_date, start_date) AS project_days
FROM projects;
SELECT task_id,
DATEDIFF(completion_date, start_date) AS task_days
FROM tasks;
This measures calendar-date distance. If a project starts and ends on the same date, the result is zero even though work occurred that day. A report requiring inclusive calendar dates may add one under a clearly defined rule.
Employee Service Days
SELECT employee_name,
joining_date,
DATEDIFF(
COALESCE(resignation_date, CURRENT_DATE),
joining_date
) AS service_days
FROM employees;
COALESCE uses today only for employees without a resignation date. That fallback is valid if NULL specifically means currently employed; otherwise it could hide incomplete data.
Defect Resolution Time
SELECT defect_id,
severity,
created_date,
resolved_date,
DATEDIFF(resolved_date, created_date) AS resolution_days
FROM defects
WHERE resolved_date IS NOT NULL;
This can support a high-level quality metric, but many defect SLAs pause while waiting for customer information, use business hours, or vary by severity. A simple date difference should not be labeled as SLA duration unless it matches the SLA definition.
Test Cycle Duration
SELECT cycle_name,
start_date,
end_date,
DATEDIFF(end_date, start_date) AS testing_days
FROM test_cycles;
The metric helps compare planned and actual cycle spans. It counts calendar-day separation and does not automatically remove weekends, holidays, blocked periods, or days when execution was paused.
Hotel Nights
Hotel check-in and check-out dates illustrate a case where ordinary date difference naturally maps to nights.
SELECT booking_id,
check_in_date,
check_out_date,
DATEDIFF(check_out_date, check_in_date) AS nights
FROM bookings;
September 20 through September 23 gives three nights. Unlike inclusive attendance counting, the checkout date is not another occupied night under a typical hotel model.
Rental and Loan Duration
SELECT rental_id,
DATEDIFF(return_date, pickup_date) AS rental_days
FROM rentals;
Actual billing may charge each started 24-hour period, each calendar date touched, or a fixed daily boundary. MySQL DATEDIFF provides a date metric, but the pricing contract determines how that metric becomes a charge.
Invoice Payment Delay
SELECT invoice_id,
due_date,
payment_date,
DATEDIFF(payment_date, due_date) AS payment_delay_days
FROM invoices;
A positive value means payment occurred after the due date, zero means the same date, and a negative value means early payment. Keep the signed value for analysis rather than immediately forcing every result to a nonnegative number.
Days Overdue
SELECT invoice_id,
due_date,
DATEDIFF(CURRENT_DATE, due_date) AS overdue_days
FROM invoices
WHERE paid_at IS NULL
AND due_date < CURRENT_DATE;
The predicate excludes unpaid invoices due today. If the organization considers an invoice overdue at the beginning or end of its due date, define that timing explicitly and use a timestamp when necessary.
Calendar Dates Versus Elapsed Duration
MySQL DATEDIFF ignores time portions and compares dates.
SELECT DATEDIFF(
'2026-09-26 00:01:00',
'2026-09-25 23:59:00'
);
-- 1
Only two minutes elapsed, but the values belong to different dates. Conversely, two timestamps 22 hours apart on the same date produce zero. This is the most important semantic distinction to understand.
Use TIMESTAMPDIFF() for Hours and Minutes
SELECT TIMESTAMPDIFF(
HOUR,
'2026-09-26 10:00:00',
'2026-09-26 15:00:00'
);
-- 5
SELECT TIMESTAMPDIFF(
MINUTE,
'2026-09-26 10:00:00',
'2026-09-26 10:45:00'
);
-- 45
TIMESTAMPDIFF() supports requested units such as YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It still has unit-specific truncation and time-zone considerations, so verify semantics for the selected types.
DATEDIFF() Versus TIMESTAMPDIFF()
| Function | Arguments | Result | Typical use |
|---|---|---|---|
DATEDIFF() | Two temporal values | Calendar days | Date-based age or duration |
TIMESTAMPDIFF() | Unit, start, end | Requested whole unit | Hours, minutes, months, years |
Notice that MySQL DATEDIFF places the ending date first for a positive duration, while TIMESTAMPDIFF uses start and end order after the unit.
DATEDIFF(), DATE_ADD(), and DATE_SUB()
These functions answer different questions.
DATE_ADD(start_date, INTERVAL 7 DAY) -- returns a future date
DATE_SUB(start_date, INTERVAL 7 DAY) -- returns an earlier date
DATEDIFF(end_date, start_date) -- returns a day count
DATE_ADD and DATE_SUB shift one temporal value by an interval. DATEDIFF compares two values and returns a number.
Inclusive Counting
From September 1 to September 3, DATEDIFF returns two because two day boundaries separate the dates. If a policy counts all dates touched, the inclusive count is three.
SELECT DATEDIFF(
'2026-09-03',
'2026-09-01'
) + 1 AS inclusive_dates;
-- 3
Do not add one automatically. Hotel nights, elapsed duration, and most interval boundaries should not use inclusive counting. Add one only when both endpoints are countable calendar dates under the domain rule.
Partial Days and Rounding Policies
DATEDIFF does not round elapsed time. It removes the time distinction by using calendar dates. If billing requires charging every started 24-hour period, calculate an elapsed duration and apply the stated ceiling rule. If operations need complete 24-hour periods, use an appropriate timestamp calculation and truncation policy.
Document rounding, time zone, and boundary ownership together. Otherwise two systems can produce different but internally consistent results.
Business Days Require a Calendar
DATEDIFF counts calendar-day separation. It does not exclude weekends, public holidays, company closures, or special working Saturdays. Business-day calculations require a calendar table with a row per date and attributes such as region, holiday name, and working-day flag.
SELECT COUNT(*) AS working_days
FROM business_calendar
WHERE calendar_date >= :start_date
AND calendar_date < :end_date
AND is_working_day = 1;
This half-open example counts eligible dates from the start through the date before the end. Adjust endpoint inclusion to the policy.
SLA Clocks and Paused Time
Real service-level agreements often use business hours and pause while a case waits for customer input. A single DATEDIFF call cannot model those clocks. Store status transitions, calculate active intervals, intersect them with the service calendar, and aggregate eligible duration.
DATEDIFF remains useful for a broad calendar-age metric, but label it accurately. Calling calendar age "SLA time" can produce false breach reports.
Age in Years Is Not Days Divided by 365
Dividing DATEDIFF by 365 does not reliably calculate age. Leap years and birthday boundaries make the result wrong around important dates.
SELECT TIMESTAMPDIFF(
YEAR,
birth_date,
CURRENT_DATE
) AS age_years
FROM person;
Even this expression requires a defined policy for unusual cases such as February 29 birthdays. Use the domain's age rule rather than approximating.
Leap Years and Calendar Boundaries
SELECT DATEDIFF('2028-03-01', '2028-02-28');
-- 2
SELECT DATEDIFF('2027-01-05', '2026-12-25');
-- 11
MySQL handles month lengths, leap days, and year transitions. Tests should still include these boundaries because application assumptions, formatting, and downstream logic can introduce errors even when the database result is correct.
DATEDIFF() and NULL
SELECT DATEDIFF(NULL, '2026-09-20');
-- NULL
SELECT DATEDIFF('2026-09-26', NULL);
-- NULL
If either date is unknown, the difference is unknown. This behavior is useful for unresolved defects, undelivered orders, and active employment records. Replace NULL only when the substitute has a documented meaning.
Using COALESCE Deliberately
SELECT defect_id,
DATEDIFF(
COALESCE(resolved_date, CURRENT_DATE),
created_date
) AS current_age_days
FROM defects;
This reports final resolution age for closed defects and current age for open ones. A label such as current_age_days is more accurate than resolution_days because unresolved rows have not been resolved.
Classifying Results with CASE
SELECT invoice_id,
due_date,
CASE
WHEN due_date >= CURRENT_DATE THEN 'Not Overdue'
WHEN DATEDIFF(CURRENT_DATE, due_date) <= 7
THEN '1-7 Days Overdue'
WHEN DATEDIFF(CURRENT_DATE, due_date) <= 30
THEN '8-30 Days Overdue'
ELSE 'More Than 30 Days Overdue'
END AS aging_bucket
FROM invoices;
Keep bucket limits mutually exclusive and test exact thresholds. For frequent reports, consider calculating the difference once in a common table expression to avoid repeating the expression.
Calculate Once and Reuse
WITH delivery_metric AS (
SELECT order_id,
DATEDIFF(delivery_date, order_date) AS delivery_days
FROM orders
WHERE delivery_date IS NOT NULL
)
SELECT order_id,
delivery_days,
CASE
WHEN delivery_days <= 3 THEN 'Fast'
WHEN delivery_days <= 7 THEN 'Normal'
ELSE 'Delayed'
END AS delivery_status
FROM delivery_metric;
This makes the definition visible once and reduces the risk that display and classification expressions diverge.
Aggregating Date Differences
SELECT AVG(DATEDIFF(resolved_date, created_date)) AS avg_days,
MIN(DATEDIFF(resolved_date, created_date)) AS min_days,
MAX(DATEDIFF(resolved_date, created_date)) AS max_days
FROM defects
WHERE resolved_date IS NOT NULL;
Average alone can hide a long tail. Include percentiles where supported, distributions by severity, sample size, and the reporting period. Decide whether reopened defects keep the original creation date or use a separate cycle model.
Grouping by Duration Buckets
WITH defect_age AS (
SELECT defect_id,
DATEDIFF(
COALESCE(resolved_date, CURRENT_DATE),
created_date
) AS age_days
FROM defects
)
SELECT CASE
WHEN age_days <= 3 THEN '0-3'
WHEN age_days <= 7 THEN '4-7'
WHEN age_days <= 30 THEN '8-30'
ELSE '31+'
END AS age_bucket,
COUNT(*) AS defect_count
FROM defect_age
GROUP BY age_bucket;
Publish the boundaries and NULL policy with the chart so readers understand what each bucket represents.
Index-Friendly Filtering
DATEDIFF is excellent in a SELECT list, but wrapping an indexed column in it can make range filtering less efficient.
-- Clear metric, potentially weaker filter pattern
WHERE DATEDIFF(CURRENT_DATE, order_date) > 30
-- Often more index-friendly
WHERE order_date < DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
The second form calculates a boundary and compares the column directly. Verify with EXPLAIN; table size, selectivity, statistics, partitioning, and selected columns still influence the plan.
DATE Versus DATETIME Boundaries
If order_date is DATETIME, comparing it with a DATE boundary implies midnight. A row exactly 30 days ago at 3:00 PM may be treated differently by a calendar-date metric and a rolling 30-day timestamp window. Match the boundary type to the requirement.
For a rolling duration, use NOW() - INTERVAL 30 DAY. For a calendar-date rule, use a date boundary and state endpoint inclusion.
Stable As-Of Dates
Dashboards and batch reports should capture their as-of date once. Binding :as_of_date makes reruns reproducible and ensures every query uses the same reference.
SELECT account_id,
DATEDIFF(:as_of_date, registered_date) AS account_age_days
FROM accounts;
The report metadata should display the same as-of date. This is particularly important around midnight and when jobs run across time zones.
MySQL Versus SQL Server DATEDIFF()
SQL Server requires a date part and uses start-then-end argument order.
-- MySQL
SELECT DATEDIFF('2026-09-26', '2026-09-20');
-- SQL Server
SELECT DATEDIFF(
DAY,
'2026-09-20',
'2026-09-26'
);
SQL Server counts boundaries of the requested date part crossed. It can request DAY, HOUR, MONTH, YEAR, and other parts, but that does not make every result an elapsed complete-unit duration. Never copy MySQL syntax into SQL Server or assume identical semantics because the function name matches.
PostgreSQL and Oracle Alternatives
PostgreSQL and Oracle commonly subtract DATE values directly.
-- PostgreSQL
SELECT DATE '2026-09-26' - DATE '2026-09-20';
-- Oracle
SELECT DATE '2026-09-26' - DATE '2026-09-20'
FROM dual;
Timestamp subtraction can return an interval, and Oracle DATE includes time-of-day behavior that differs from a date-only conceptual type. Portability work must compare input types and semantics, not only translate syntax.
Cross-Database Comparison
| Database | Typical day difference | Important detail |
|---|---|---|
| MySQL | DATEDIFF(end, start) | Uses date portions |
| SQL Server | DATEDIFF(DAY, start, end) | Counts date-part boundaries |
| PostgreSQL | end_date - start_date | DATE subtraction returns days |
| Oracle | end_date - start_date | DATE values can include time |
Invalid Input and SQL Modes
Ambiguous strings, invalid dates, and unexpected coercion can lead to warnings, errors, or NULL depending on value and configuration. Bind typed parameters through the database driver and use unambiguous ISO-style literals in examples. Strict modes help reveal invalid data earlier.
Time Zones and Session Context
DATE values have no time zone, but deriving dates from TIMESTAMP values can depend on the session time zone. The same instant may belong to different calendar dates in Chicago and Chennai. If a business metric is local-date based, convert to the authoritative named zone before extracting or comparing dates.
Store the zone policy with the metric definition. A raw numeric offset is insufficient for historical and future daylight-saving rules.
Open Versus Closed Lifecycle Metrics
An open record has no final duration yet. Mixing current age and completed duration in one unlabeled average creates a misleading metric. Report them separately or include a clear status dimension.
SELECT status,
AVG(
DATEDIFF(
COALESCE(closed_date, :as_of_date),
opened_date
)
) AS age_or_duration_days
FROM support_case
GROUP BY status;
For closed cases, the expression measures final calendar duration. For open cases, it measures age at the reporting date. The column label and chart legend should explain that distinction. A more rigorous dataset may expose separate current_age_days and completed_duration_days fields.
Detecting Invalid Date Sequences
A negative result can reveal data-quality problems when the domain requires an end date on or after its start date.
SELECT order_id,
shipped_date,
delivered_date,
DATEDIFF(delivered_date, shipped_date) AS shipping_days
FROM orders
WHERE delivered_date IS NOT NULL
AND DATEDIFF(delivered_date, shipped_date) < 0;
Use a direct comparison such as delivered_date < shipped_date for the filter when possible. Keep DATEDIFF in the SELECT list to quantify the anomaly. Prevent invalid states with application validation, constraints where supported, and transactionally consistent updates rather than relying only on after-the-fact reports.
Customer Cohort Analysis
Account age can be combined with a registration cohort to compare customers at similar lifecycle stages.
SELECT EXTRACT(YEAR_MONTH FROM registered_date) AS cohort_month,
COUNT(*) AS customers,
AVG(DATEDIFF(:as_of_date, registered_date)) AS avg_age_days
FROM customers
WHERE registered_date <= :as_of_date
GROUP BY EXTRACT(YEAR_MONTH FROM registered_date)
ORDER BY cohort_month;
The as-of date ensures a stable snapshot. Cohorts should normally exclude future or invalid registration dates, and analysts should avoid comparing partial recent cohorts with mature cohorts without explaining the difference.
Aging Snapshots Over Time
Calculating age from today's date makes a live dashboard useful, but it does not preserve what the dashboard showed last month. Historical trend analysis needs snapshots or reconstructable event history.
INSERT INTO defect_age_snapshot (
snapshot_date,
defect_id,
status,
age_days
)
SELECT :snapshot_date,
defect_id,
status,
DATEDIFF(:snapshot_date, created_date)
FROM defects
WHERE created_date <= :snapshot_date
AND (resolved_date IS NULL OR resolved_date > :snapshot_date);
A snapshot records the metric definition at a known date. Alternatively, a complete event model can reconstruct state, but that approach requires reliable historical transitions and more complex queries.
Median and Percentile Durations
Duration data is often skewed: most cases close quickly while a few remain open for months. An average can rise sharply because of a small long tail. Where the database version supports appropriate window or percentile functions, publish median and high-percentile values alongside the average.
When MySQL lacks the exact analytic function needed for the selected version, calculate percentiles in a reporting engine or use a carefully reviewed SQL technique. Do not present maximum duration as a typical experience. Always show sample size and filter out records that do not meet the metric's completion rule.
Percentage Meeting a Calendar-Day Target
Conditional aggregation can turn date differences into a compliance rate for a genuinely calendar-day-based target.
SELECT COUNT(*) AS completed_cases,
SUM(
CASE
WHEN DATEDIFF(closed_date, opened_date) <= 3
THEN 1
ELSE 0
END
) AS within_target,
100.0 * SUM(
DATEDIFF(closed_date, opened_date) <= 3
) / COUNT(*) AS within_target_percent
FROM support_case
WHERE closed_date IS NOT NULL;
Confirm boolean-expression behavior for the target DBMS and guard against an empty denominator. Severity-specific targets should be joined from governed configuration instead of embedded as one universal threshold.
Duration Metrics in API Contracts
An API field named duration is ambiguous. Prefer a name such as calendarDaysSinceRegistration, calendarDaysRemaining, or hotelNights. Document argument direction, endpoint inclusion, as-of date, time zone, and NULL behavior.
If clients need exact elapsed duration, return an instant pair or an explicitly defined unit rather than a DATEDIFF-derived day count. Version the contract when semantics change; silently redefining a field from calendar days to elapsed 24-hour blocks can break client decisions even when the JSON type remains numeric.
Storing Versus Calculating Day Differences
A duration between two immutable stored dates can be calculated when read or materialized for reporting. A value based on CURRENT_DATE changes every day, so storing it creates a maintenance requirement and risks stale data.
For high-volume analytics, snapshot tables or warehouse transformations can intentionally materialize age with an associated as-of date. In transactional tables, usually store authoritative start and end values and derive the difference. If a generated column or functional index is considered, check MySQL determinism restrictions and confirm that the expression excludes volatile current-time functions.
Validating an Index-Friendly Rewrite
Rewriting a DATEDIFF filter as a direct range comparison improves index access only if the two expressions select the same boundary set.
-- Calendar-date metric
DATEDIFF(CURRENT_DATE, created_at) <= 30
-- Candidate range rewrite
created_at >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
If created_at is DATETIME, the first expression ignores time while the second compares time with midnight. Test rows on the cutoff date at 00:00:00 and 23:59:59. The exact equivalent may require an upper boundary or an explicit date policy. Performance should never be improved by silently changing the result set.
Migration and Portability Tests
When moving date logic between databases, create a small conformance dataset rather than translating function names mechanically. Include same-date times, values across midnight, leap day, month end, negative order, NULL, time-zone conversion, and fractional timestamps.
Compare both result values and result types. SQL Server boundary counting, PostgreSQL intervals, Oracle DATE time components, and MySQL date-part behavior can disagree in edge cases. Keeping expected outcomes in automated migration tests makes those differences visible before production reports change.
Auditability and Metric Definitions
A reliable metric should be reproducible from stored inputs and a versioned definition. Record the as-of date, source time zone, selected population, status rules, endpoint inclusion, and whether NULL values were excluded or substituted. If the policy changes, keep the effective date of the new definition.
This governance matters most when DATEDIFF results influence payments, compliance, staffing, or service penalties. The SQL expression may be only one line, but the meaning surrounding that line determines whether the result can be trusted.
Testing DATEDIFF()
Use fixed dates in automated tests. Cover positive, negative, and zero results; same-date timestamps with different times; timestamps a few minutes apart across midnight; month and year boundaries; February 29; NULL inputs; inclusive counting; exact bucket thresholds; and the chosen time zone.
For filter rewrites, prove that the range predicate selects the same rows as the original metric under the stated DATE or DATETIME semantics. Compare execution plans as well as results.
Common Mistakes
- Reversing the end and start arguments.
- Expecting MySQL DATEDIFF to return hours or minutes.
- Assuming calendar-day difference equals elapsed 24-hour periods.
- Adding one for inclusive counting without a business requirement.
- Using calendar days where an SLA requires business time.
- Calculating age by dividing day difference by 365.
- Replacing NULL end dates without documenting the fallback.
- Wrapping indexed columns in DATEDIFF for large filters.
- Ignoring the time zone used to derive calendar dates.
- Assuming MySQL and SQL Server DATEDIFF syntax is identical.
Best-Practice Checklist
- Name the end and start values before choosing argument order.
- Define whether the metric means calendar dates or elapsed duration.
- Use TIMESTAMPDIFF when a non-day unit is required in MySQL.
- Document endpoint inclusion and whether zero is meaningful.
- Use a business calendar for working-day rules.
- Preserve NULL when the duration is genuinely unknown.
- Bind a stable as-of date for reproducible reporting.
- Prefer direct range predicates for indexed filtering.
- State the authoritative time zone for local-date metrics.
- Test leap days, midnight boundaries, exact thresholds, and portability.
Interview Questions and Answers
What does MySQL DATEDIFF return? It returns the signed number of calendar days between the date portions of two temporal values.
What is the argument order? MySQL calculates the first argument minus the second, often written as end date minus start date.
Can it return a negative number? Yes. The result is negative when the first date is earlier.
Does MySQL DATEDIFF consider time? No. It uses the date portions, so two times on the same date return zero.
How do you calculate hours? Use TIMESTAMPDIFF(HOUR, start_time, end_time).
How are business days calculated? Use a maintained business-calendar table and count eligible dates.
Why can a range predicate be faster? It can leave an indexed date column unwrapped for a range scan.
Is SQL Server DATEDIFF the same? No. SQL Server includes a date-part argument, uses start then end, and counts requested date-part boundaries.
Complete Defect Aging Example
WITH defect_metric AS (
SELECT defect_id,
severity,
status,
DATEDIFF(
COALESCE(resolved_date, :as_of_date),
created_date
) AS age_days
FROM defects
WHERE created_date <= :as_of_date
)
SELECT severity,
status,
COUNT(*) AS defect_count,
AVG(age_days) AS average_age_days,
MAX(age_days) AS oldest_age_days
FROM defect_metric
GROUP BY severity, status
ORDER BY severity, status;
This example uses one stable reporting date, retains separate severity and status groups, and calculates age once. The output is calendar age, not business-time SLA duration, so the report should use that precise label.
Key Takeaway
MySQL DATEDIFF() compares two temporal values and returns their signed calendar-day difference. It works well for age, countdown, duration, delay, and reporting metrics when date boundaries are the intended unit.
RELIABLE DATEDIFF LOGIC = CORRECT ARGUMENT ORDER
+ CALENDAR-DAY DEFINITION
+ EXPLICIT INCLUSION RULE
+ NULL POLICY
+ TIME-ZONE CONTEXT
+ INDEX-AWARE FILTERS
+ BOUNDARY TESTS
Do not confuse date separation with elapsed hours, working days, or inclusive endpoint counts. Choose TIMESTAMPDIFF for other units, a calendar table for business days, and direct range predicates for efficient filters. Those distinctions make date metrics accurate, explainable, and portable.