DATEDIFF()
Returns the difference between two dates in a specified unit.
Description
DATEDIFF computes the difference between two dates or timestamps in a chosen
unit — days, months, years, etc. It answers "how many days between these two
dates?" without manual arithmetic, and it respects calendar rules.
Syntax
DATEDIFF('day', start, end)
DATEDIFF('month', start, end)Parameters
| Name | Description | Optional |
|---|---|---|
| unit | The unit: day, week, month, year, hour, minute, second. | No |
| start | The starting date. | No |
| end | The ending date. | No |
Return Type
Returns an INTEGER (can be negative if end is before start).
Examples
Employees & Departments
Loading database engine...
Employees & Departments
Loading database engine...
DATEDIFF vs subtraction
end - start gives a raw interval; DATEDIFF('day', start, end) gives a clean
integer count in the unit you want.
Common Mistakes
- Argument order.
DATEDIFF(unit, start, end)— swapping start/end flips the sign. - Unit spelling. Unit strings vary by engine ('day' vs 'd'); check yours.
- Fractional results.
DATEDIFFreturns whole units; partial periods are truncated, not rounded.
Related Functions
See also: DATE_TRUNC, EXTRACT, DATE_ADD.