Eric Campbell 5 min read Engineering

SQL - DATEADD() / DATE_ADD() - Add Interval to Date

Date arithmetic is fundamental to almost every production database. You'll calculate subscription renewals, find overdue invoices, generate reporting periods, and implement data retention policies....

Key Insights

  • Date arithmetic syntax varies significantly across databases: SQL Server uses DATEADD(), MySQL uses DATE_ADD() or + INTERVAL, and PostgreSQL uses + INTERVAL exclusively—knowing these differences prevents frustrating debugging sessions.
  • Month-end edge cases will bite you: adding one month to January 31st produces different results across databases, so always test boundary conditions in your date calculations.
  • Negative intervals work universally for subtracting dates, making functions like DATEADD(day, -90, GETDATE()) the cleanest way to query historical data ranges.

Introduction & Syntax Overview

Date arithmetic is fundamental to almost every production database. You’ll calculate subscription renewals, find overdue invoices, generate reporting periods, and implement data retention policies. The challenge? Every major database handles this differently.

Here’s the syntax comparison you need to memorize:

-- SQL Server: DATEADD(interval, number, date)
SELECT DATEADD(day, 7, '2024-01-15');
-- Result: 2024-01-22

-- MySQL: DATE_ADD(date, INTERVAL number unit)
SELECT DATE_ADD('2024-01-15', INTERVAL 7 DAY);
-- Result: 2024-01-22

-- MySQL alternative syntax
SELECT '2024-01-15' + INTERVAL 7 DAY;
-- Result: 2024-01-22

-- PostgreSQL: date + INTERVAL 'number unit'
SELECT '2024-01-15'::date + INTERVAL '7 days';
-- Result: 2024-01-22 00:00:00

-- PostgreSQL shorthand
SELECT '2024-01-15'::date + 7;  -- Adds days by default
-- Result: 2024-01-22

Notice that SQL Server puts the interval unit first, MySQL puts the date first, and PostgreSQL uses a string-based interval syntax. These differences matter when you’re writing queries that need to work across environments or when you’re switching between projects.

Interval Units Reference

Each database supports a core set of interval units, plus some database-specific options:

-- SQL Server interval units
SELECT 
    DATEADD(year, 1, '2024-01-15')      AS plus_year,      -- 2025-01-15
    DATEADD(quarter, 1, '2024-01-15')   AS plus_quarter,   -- 2024-04-15
    DATEADD(month, 1, '2024-01-15')     AS plus_month,     -- 2024-02-15
    DATEADD(week, 1, '2024-01-15')      AS plus_week,      -- 2024-01-22
    DATEADD(day, 1, '2024-01-15')       AS plus_day,       -- 2024-01-16
    DATEADD(hour, 1, '2024-01-15')      AS plus_hour,      -- 2024-01-15 01:00
    DATEADD(minute, 30, '2024-01-15')   AS plus_minute,    -- 2024-01-15 00:30
    DATEADD(second, 45, '2024-01-15')   AS plus_second;    -- 2024-01-15 00:00:45

-- MySQL interval units
SELECT 
    DATE_ADD('2024-01-15', INTERVAL 1 YEAR) AS plus_year,
    DATE_ADD('2024-01-15', INTERVAL 1 QUARTER) AS plus_quarter,
    DATE_ADD('2024-01-15', INTERVAL 1 MONTH) AS plus_month,
    DATE_ADD('2024-01-15', INTERVAL 1 WEEK) AS plus_week,
    DATE_ADD('2024-01-15', INTERVAL 1 DAY) AS plus_day,
    DATE_ADD('2024-01-15 10:00:00', INTERVAL 1 HOUR) AS plus_hour,
    DATE_ADD('2024-01-15 10:00:00', INTERVAL 30 MINUTE) AS plus_minute,
    DATE_ADD('2024-01-15 10:00:00', INTERVAL 45 SECOND) AS plus_second;

-- PostgreSQL interval units
SELECT 
    '2024-01-15'::date + INTERVAL '1 year' AS plus_year,
    '2024-01-15'::date + INTERVAL '3 months' AS plus_quarter,  -- No QUARTER keyword
    '2024-01-15'::date + INTERVAL '1 month' AS plus_month,
    '2024-01-15'::date + INTERVAL '1 week' AS plus_week,
    '2024-01-15'::date + INTERVAL '1 day' AS plus_day,
    '2024-01-15 10:00:00'::timestamp + INTERVAL '1 hour' AS plus_hour,
    '2024-01-15 10:00:00'::timestamp + INTERVAL '30 minutes' AS plus_minute,
    '2024-01-15 10:00:00'::timestamp + INTERVAL '45 seconds' AS plus_second;

SQL Server also supports dayofyear, weekday, millisecond, microsecond, and nanosecond. MySQL adds compound intervals like YEAR_MONTH and DAY_HOUR. PostgreSQL allows arbitrary combinations within a single interval string.

Basic Usage Examples

Real applications need both future and past date calculations. Use positive numbers for future dates and negative numbers for past dates:

-- SQL Server: Calculate business dates
SELECT 
    order_id,
    order_date,
    DATEADD(day, 3, order_date) AS estimated_ship_date,
    DATEADD(day, 7, order_date) AS estimated_delivery,
    DATEADD(day, 30, order_date) AS payment_due_date
FROM orders
WHERE order_date >= DATEADD(day, -7, GETDATE());  -- Orders from last 7 days

-- MySQL: Subscription management
SELECT 
    user_id,
    subscription_start,
    DATE_ADD(subscription_start, INTERVAL 1 MONTH) AS next_billing_date,
    DATE_ADD(subscription_start, INTERVAL 1 YEAR) AS annual_renewal_date
FROM subscriptions
WHERE DATE_ADD(subscription_start, INTERVAL 1 MONTH) <= CURDATE();  -- Due for billing

-- PostgreSQL: Content expiration
SELECT 
    article_id,
    published_at,
    published_at + INTERVAL '90 days' AS archive_date,
    published_at + INTERVAL '1 year' AS deletion_date
FROM articles
WHERE published_at + INTERVAL '90 days' < CURRENT_DATE;  -- Ready to archive

Subtracting dates is equally straightforward:

-- SQL Server: Find records from the past
SELECT * FROM audit_logs 
WHERE created_at >= DATEADD(month, -3, GETDATE());  -- Last 3 months

-- MySQL: Historical comparison
SELECT * FROM sales 
WHERE sale_date BETWEEN 
    DATE_ADD(CURDATE(), INTERVAL -30 DAY) 
    AND CURDATE();

-- PostgreSQL: Retention policy
DELETE FROM session_logs 
WHERE created_at < CURRENT_TIMESTAMP - INTERVAL '7 days';

Working with Time Components

When scheduling matters at the hour or minute level, time-based intervals become essential:

-- SQL Server: Appointment scheduling
SELECT 
    appointment_id,
    start_time,
    DATEADD(minute, 30, start_time) AS end_time,
    DATEADD(minute, -15, start_time) AS reminder_time
FROM appointments
WHERE start_time BETWEEN GETDATE() AND DATEADD(hour, 24, GETDATE());

-- MySQL: Session timeout management
SELECT 
    session_id,
    last_activity,
    DATE_ADD(last_activity, INTERVAL 30 MINUTE) AS expires_at
FROM user_sessions
WHERE DATE_ADD(last_activity, INTERVAL 30 MINUTE) < NOW();  -- Expired sessions

-- PostgreSQL: Time slot generation
SELECT 
    generate_series(
        '2024-01-15 09:00:00'::timestamp,
        '2024-01-15 17:00:00'::timestamp,
        INTERVAL '30 minutes'
    ) AS slot_start;

Combining multiple intervals handles complex scheduling requirements:

-- MySQL: Compound intervals
SELECT DATE_ADD('2024-01-15 10:00:00', INTERVAL '1:30' HOUR_MINUTE);
-- Result: 2024-01-15 11:30:00

-- PostgreSQL: Multiple intervals in one expression
SELECT '2024-01-15'::date + INTERVAL '1 month 2 weeks 3 days';
-- Result: 2024-03-01 00:00:00

Common Real-World Applications

These patterns appear constantly in production systems:

-- Rolling 30-day window for analytics (SQL Server)
SELECT 
    COUNT(*) AS signups_last_30_days,
    COUNT(CASE WHEN created_at >= DATEADD(day, -7, GETDATE()) THEN 1 END) AS signups_last_7_days
FROM users
WHERE created_at >= DATEADD(day, -30, GETDATE());

-- Data retention cleanup (MySQL)
DELETE FROM event_logs 
WHERE created_at < DATE_ADD(CURDATE(), INTERVAL -90 DAY);

-- Next billing date calculation with trial period (PostgreSQL)
SELECT 
    user_id,
    created_at,
    CASE 
        WHEN trial_ends_at > CURRENT_DATE 
        THEN trial_ends_at 
        ELSE created_at + INTERVAL '1 month' * billing_cycles_completed
    END AS next_billing_date
FROM subscriptions;

-- Generate monthly reporting periods (PostgreSQL)
SELECT 
    date_trunc('month', d) AS period_start,
    date_trunc('month', d) + INTERVAL '1 month' - INTERVAL '1 day' AS period_end
FROM generate_series(
    '2024-01-01'::date,
    '2024-12-01'::date,
    INTERVAL '1 month'
) AS d;

Edge Cases & Pitfalls

Month-end handling is where databases diverge and bugs hide:

-- What happens when you add 1 month to January 31?
-- SQL Server
SELECT DATEADD(month, 1, '2024-01-31');  -- 2024-02-29 (leap year)
SELECT DATEADD(month, 1, '2023-01-31');  -- 2023-02-28 (non-leap year)

-- MySQL
SELECT DATE_ADD('2024-01-31', INTERVAL 1 MONTH);  -- 2024-02-29
SELECT DATE_ADD('2024-01-30', INTERVAL 1 MONTH);  -- 2024-02-29 (clamped!)

-- PostgreSQL
SELECT '2024-01-31'::date + INTERVAL '1 month';  -- 2024-02-29

All three databases clamp to the last valid day of the target month. But watch what happens when you chain operations:

-- SQL Server: Adding then subtracting doesn't always round-trip
SELECT DATEADD(month, -1, DATEADD(month, 1, '2024-01-31'));
-- Result: 2024-01-29 (NOT 2024-01-31!)

Leap year considerations affect day-of-year calculations:

-- SQL Server: Day 60 is different in leap vs non-leap years
SELECT DATEADD(day, 59, '2024-01-01');  -- 2024-02-29 (leap year)
SELECT DATEADD(day, 59, '2023-01-01');  -- 2023-03-01 (non-leap year)

For timezone-aware applications, always use timestamp with time zone types and be explicit about conversions:

-- PostgreSQL: Timezone-aware arithmetic
SELECT 
    '2024-03-10 01:30:00 America/New_York'::timestamptz + INTERVAL '1 hour';
-- Correctly handles DST transition

Cross-Database Compatibility Tips

When you need portable date arithmetic, abstract it through views, functions, or your ORM:

-- Create a compatibility view (PostgreSQL example that mimics SQL Server)
CREATE OR REPLACE FUNCTION dateadd(unit text, num integer, dt timestamp)
RETURNS timestamp AS $$
BEGIN
    RETURN CASE unit
        WHEN 'day' THEN dt + (num || ' days')::interval
        WHEN 'month' THEN dt + (num || ' months')::interval
        WHEN 'year' THEN dt + (num || ' years')::interval
        WHEN 'hour' THEN dt + (num || ' hours')::interval
        WHEN 'minute' THEN dt + (num || ' minutes')::interval
        ELSE dt
    END;
END;
$$ LANGUAGE plpgsql;

-- Now works like SQL Server
SELECT dateadd('day', 7, CURRENT_TIMESTAMP);

For application code, most ORMs provide database-agnostic date functions. In Django:

from django.db.models import F
from django.db.models.functions import Now
from datetime import timedelta

# Works across all supported databases
Orders.objects.filter(
    created_at__gte=Now() - timedelta(days=30)
)

The cleanest approach: handle date arithmetic in your application layer when possible, and use database-specific functions only for set-based operations that must run in SQL for performance reasons.

Liked this? There's more.

Every week: one practical technique, explained simply, with code you can use immediately.