ConvertSite
Date functions

DATEDIFF

Calculates the difference between two dates in the specified unit. Use DATEDIFF to find age, subscription length, days until deadline, or any time interval between two dates.

Syntax

DATEDIFF(startDate, endDate, unit)

  • startDate (Date or DateTime): The earlier date

  • endDate (Date or DateTime): The later date

  • unit (String): The unit to return — "years", "months", "days", "hours", "minutes", "seconds"

Returns

Number representing the difference in the specified unit. Returns whole units only.

Examples

Calculate age

DATEDIFF(BIRTH_DATE, CURRDATE(), "years")

Returns the person's age in whole years.

Days until deadline

DATEDIFF(CURRDATE(), DEADLINE, "days")

Shows how many days remain until the deadline.

Subscription length in months

DATEDIFF(START_DATE, END_DATE, "months")

Calculates the duration of a subscription in months.

Hours since submission

DATEDIFF(SUBMISSION_TIME, CURRDATETIME(), "hours")

Returns hours elapsed since a form was submitted.

Age verification (18+)

IF(DATEDIFF(BIRTH_DATE, CURRDATE(), "years") >= 18, "Approved", "Must be 18+")

Returns whole units. Years and months account for calendar boundaries — February 1 to March 1 is 1 month, not 28-31 days.

Parameter order matters: (start, end, unit). Start is the earlier date, end is the later date.

Unit Reference

  • "years" — Whole years between dates (accounts for leap years)

  • "months" — Whole months between dates (accounts for different month lengths)

  • "days" — Total days between dates

  • "hours" — Total hours between datetimes

  • "minutes" — Total minutes between datetimes

  • "seconds" — Total seconds between datetimes

Common Patterns

Trial period remaining:

TRIAL_END = ADDDAYS(SIGNUP_DATE, 30)
DAYS_LEFT = DATEDIFF(CURRDATE(), TRIAL_END, "days")
IF(DAYS_LEFT

Years of service:

DATEDIFF(HIRE_DATE, CURRDATE(), "years")

Late fee calculation:

DAYS_LATE = DATEDIFF(DUE_DATE, CURRDATE(), "days")
IF(DAYS_LATE > 0, DAYS_LATE * 5, 0)

Response time in hours:

DATEDIFF(REQUEST_TIME, RESPONSE_TIME, "hours")

Subscription months billed:

MONTHS_ACTIVE = DATEDIFF(START_DATE, CURRDATE(), "months")
TOTAL_BILLED = MONTHS_ACTIVE * MONTHLY_RATE

Common Mistakes

  • Wrong parameter order: It's (start, end, unit), not (end, start, unit). Reversing them gives negative results.

  • Forgetting quotes on unit: Use "days" not days. Without quotes, it errors.

  • Mixing date types: Both dates should be the same type (DATE or DATETIME). Mixing can cause unexpected results.

  • Expecting partial units: DATEDIFF returns whole units only. 1.8 years rounds down to 1.

  • Using hours/minutes/seconds with DATE: Time units require DATETIME values. Use CURRDATETIME instead of CURRDATE.

See Also

DAYS · ADDDAYS · ADDMONTHS · ADDYEARS · CURRDATE · CURRDATETIME

Was this helpful?
Was this helpful?