Date Calculations: DATEADD, DATEDIFF, ISDATE

"The next match is in 10 days." "How many years since Dhoni's debut?" "Is this value even a real date?" These are date calculations and validation.

Definition

DATEADD(part, interval, date) returns a new date obtained by adding interval units of part (year, month, day, hour …) to date. A negative interval subtracts.

DATEDIFF(part, start_date, end_date) returns an INT: the number of part boundaries crossed between start_date and end_date. If end_date is earlier, the result is negative.

ISDATE(value) returns 1 if the value can be converted to a valid date/time, otherwise 0.

"Boundaries crossed" is the key idea of DATEDIFF: DATEDIFF(year, …) counts how many times you pass 1 January, not how many full 365-day years have passed.

Syntax

DATEADD(part, interval, date)          -- interval: positive = add, negative = subtract
DATEDIFF(part, start_date, end_date)   -- result = end - start, in whole "part" units
ISDATE(value)                          -- 1 = valid date, 0 = not a date

Example — DATEADD

SELECT
    DATEADD(year, 2, '2025-08-20')            AS plus_2_years,
    DATEADD(month, -4, '2025-08-20')          AS minus_4_months,
    DATEADD(day, 10, '2025-08-20')            AS next_match,
    DATEADD(hour, 3, '2025-08-20 18:55:45')   AS plus_3_hours;

Note: In SQL Server a string literal like '2025-08-20' is treated as DATETIME, so the results display with a time, e.g. 2027-08-20 00:00:00.000. On a DATE column the result stays a plain date.

Example — DATEDIFF

SELECT
    DATEDIFF(year,  '2008-04-20', '2025-08-20') AS years,
    DATEDIFF(month, '2008-04-20', '2025-08-20') AS months,
    DATEDIFF(day,   '2008-04-20', '2025-08-20') AS days;

How long since Virat Kohli's debut (2008-04-20) on 2025-08-20? 17 year boundaries, 208 month boundaries, 6331 days.

The boundary trap

SELECT
    DATEDIFF(year,  '2024-12-31', '2025-01-01') AS years,
    DATEDIFF(day,   '2024-12-31', '2025-01-01') AS days;

Only one day passed, but DATEDIFF(year) says 1 because a year boundary (1 January) was crossed. For an exact age, compare the day too, or use DATEDIFF(day, …) / 365 as an approximation.

Example — next match and days since debut

SELECT
    name,
    debut,
    DATEADD(year, 10, debut)            AS ten_year_mark,
    DATEDIFF(day, debut, '2025-08-20')  AS days_since
FROM player_profiles;

Example — ISDATE

SELECT
    ISDATE('2025-08-20') AS valid_date,
    ISDATE('2025-13-45') AS bad_month,
    ISDATE('hello')      AS not_a_date;

Use ISDATE to clean dirty data first — for example, to find rows in an imported text column that would break a CAST(... AS DATE): WHERE ISDATE(raw_date) = 0. SQL Server also rejects impossible days such as '2025-02-30' (returns 0); the browser playground's emulation is more lenient on such edge cases.

Common mistakes

• ❌ DATEDIFF(day, '2025-08-20', '2008-04-20') → a negative number. ✅ Start date first, end date second.
• ❌ Using DATEDIFF(year, birth, today) as an exact age — it can be 1 too high before the birthday.
• ❌ debut + 10 to add days. ✅ DATEADD(day, 10, debut) — explicit and works on every date type.
• ❌ Putting quotes around the part: DATEADD('day', 10, d). ✅ DATEADD(day, 10, d).

SQL Server vs MySQL / PostgreSQL

Interview tip

Q: What does DATEDIFF(month, '2025-01-31', '2025-02-01') return? 1 — only one day passed, but a month boundary was crossed. DATEDIFF counts boundaries, not full periods.