Date Arithmetic Functions
Add or subtract time intervals from dates and datetimes. Use these functions to calculate deadlines, schedule events, or build date-based logic like subscription renewals or payment schedules.
Functions
ADDDAYS — Add or subtract days
ADDMONTHS — Add or subtract months
ADDYEARS — Add or subtract years
ADDHOURS — Add or subtract hours
ADDMINUTES — Add or subtract minutes
ADDSECONDS — Add or subtract seconds
ADDDAYS
Syntax: ADDDAYS(dateOrDateTime, days)
Adds (or subtracts with negative numbers) days to a date or datetime.
Examples
Calculate delivery date:
ADDDAYS(ORDER_DATE, 5)Due date 30 days from now:
ADDDAYS(CURRDATE(), 30)Find date 7 days ago:
ADDDAYS(CURRDATE(), -7)Payment schedule (every 15 days):
PAYMENT_1 = ADDDAYS(START_DATE, 15)
PAYMENT_2 = ADDDAYS(START_DATE, 30)
PAYMENT_3 = ADDDAYS(START_DATE, 45)Works with both DATE and DATETIME values. The result type matches the input.
ADDMONTHS
Syntax: ADDMONTHS(dateOrDateTime, months)
Adds (or subtracts) months to a date or datetime. Handles month-end dates correctly.
Examples
Calculate quarterly review date:
ADDMONTHS(START_DATE, 3)Annual subscription renewal:
ADDMONTHS(SUBSCRIPTION_START, 12)6 months ago:
ADDMONTHS(CURRDATE(), -6)Monthly payment schedule:
NEXT_PAYMENT = ADDMONTHS(LAST_PAYMENT, 1)Automatically adjusts for months with different day counts. January 31 + 1 month = February 28 (or 29 in leap years).
ADDYEARS
Syntax: ADDYEARS(dateOrDateTime, years)
Adds (or subtracts) years to a date or datetime.
Examples
License expiration (3-year term):
ADDYEARS(ISSUE_DATE, 3)Calculate 18th birthday:
ADDYEARS(BIRTH_DATE, 18)Contract renewal date:
ADDYEARS(CONTRACT_START, 1)Historical comparison:
SALES_LAST_YEAR = ADDYEARS(CURRDATE(), -1)Leap year dates adjust automatically. February 29, 2024 + 1 year = February 28, 2025.
ADDHOURS
Syntax: ADDHOURS(dateTime, hours)
Adds (or subtracts) hours to a datetime. Requires a DATETIME value, not just a DATE.
Examples
Meeting end time (2-hour meeting):
ADDHOURS(MEETING_START, 2)Countdown 24 hours from now:
ADDHOURS(CURRDATETIME(), 24)Shift end time:
ADDHOURS(SHIFT_START, 8)Time zone conversion (EST to PST):
ADDHOURS(EVENT_TIME_EST, -3)Requires a DATETIME value, not just a DATE. Use DATETIME() to convert if needed.
ADDMINUTES
Syntax: ADDMINUTES(dateTime, minutes)
Adds (or subtracts) minutes to a datetime.
Examples
Appointment end time (45-minute session):
ADDMINUTES(APPOINTMENT_START, 45)Buffer time before event:
ARRIVAL_TIME = ADDMINUTES(EVENT_START, -15)Break time:
BREAK_END = ADDMINUTES(BREAK_START, 30)ADDSECONDS
Syntax: ADDSECONDS(dateTime, seconds)
Adds (or subtracts) seconds to a datetime.
Examples
Add processing delay:
ADDSECONDS(REQUEST_TIME, 90)Countdown timer:
EXPIRES_AT = ADDSECONDS(CURRDATETIME(), 300)Most business logic uses days, months, or hours. ADDSECONDS is useful for technical timing or precise intervals.
Common Patterns
30-day trial period:
TRIAL_END = ADDDAYS(SIGNUP_DATE, 30)
DAYS_REMAINING = DAYS(CURRDATE(), TRIAL_END)Monthly billing cycle:
NEXT_BILL_DATE = ADDMONTHS(LAST_BILL_DATE, 1)
IF(CURRDATE() >= NEXT_BILL_DATE, "Payment due", "Active")Event starts in 2 hours:
EVENT_START = ADDHOURS(CURRDATETIME(), 2)
`Event starts at ${HOUR(EVENT_START)}:${MINUTE(EVENT_START)}`Annual license with grace period:
EXPIRATION = ADDYEARS(PURCHASE_DATE, 1)
GRACE_END = ADDDAYS(EXPIRATION, 14)Business days calculation (skip weekends):
ESTIMATED_DATE = ADDDAYS(ORDER_DATE, 5)
IF(WEEKDAY(ESTIMATED_DATE) = 1, ADDDAYS(ESTIMATED_DATE, 1),
IF(WEEKDAY(ESTIMATED_DATE) = 7, ADDDAYS(ESTIMATED_DATE, 2),
ESTIMATED_DATE))Common Mistakes
Using ADDHOURS with DATE: Hour/minute/second functions require DATETIME, not DATE. Convert first with DATETIME().
Forgetting negative numbers for subtraction: Use ADDDAYS(date, -7), not SUBTRACTDAYS(date, 7).
Month-end edge cases: ADDMONTHS handles them automatically, but be aware January 31 + 1 month ≠ March 3.
Mixing date types: Ensure your input is DATE or DATETIME as required by each function.
See Also
DATE · DATETIME · CURRDATE · CURRDATETIME · DATEDIFF · DAYS