SQL DATEDIFF: Complete Guide with Interactive Examples

-- Conceptual syntax (varies by database)
DATEDIFF(unit, start_date, end_date)
-- or
DATEDIFF(end_date, start_date)

-- DATEDIFF(datepart, start_date, end_date)
SELECT DATEDIFF(day, '2024-01-01', '2024-01-15') as days_diff;
-- Returns: 14

SELECT DATEDIFF(month, '2024-01-15', '2024-06-15') as months_diff;
-- Returns: 5

-- DATEDIFF(end_date, start_date) - returns days only
SELECT DATEDIFF('2024-01-15', '2024-01-01') as days_diff;
-- Returns: 14

-- For other units, use TIMESTAMPDIFF
SELECT TIMESTAMPDIFF(MONTH, '2024-01-15', '2024-06-15') as months_diff;
-- Returns: 5

-- Direct subtraction returns an interval
SELECT '2024-01-15'::date - '2024-01-01'::date as days_diff;
-- Returns: 14

-- Or use AGE() for detailed breakdown
SELECT AGE('2024-06-15', '2024-01-15');
-- Returns: 5 mons

-- Subtract Julian days for date difference
SELECT julianday('2024-01-15') - julianday('2024-01-01') as days_diff;
-- Returns: 14.0

-- Cast to integer for whole days
SELECT CAST(julianday('2024-01-15') - julianday('2024-01-01') AS INTEGER);
-- Returns: 14

-- SQL Server
SELECT 
  order_id,
  order_date,
  ship_date,
  DATEDIFF(day, order_date, ship_date) as fulfillment_days
FROM orders;

-- MySQL
SELECT 
  order_id,
  order_date,
  ship_date,
  DATEDIFF(ship_date, order_date) as fulfillment_days
FROM orders;

-- SQLite
SELECT 
  order_id,
  order_date,
  ship_date,
  CAST(julianday(ship_date) - julianday(order_date) AS INTEGER) as fulfillment_days
FROM orders;

-- Find invoices overdue as of today
-- SQL Server
SELECT 
  invoice_id,
  due_date,
  DATEDIFF(day, due_date, GETDATE()) as days_overdue
FROM invoices
WHERE due_date < GETDATE();

-- MySQL
SELECT 
  invoice_id,
  due_date,
  DATEDIFF(CURDATE(), due_date) as days_overdue
FROM invoices
WHERE due_date < CURDATE();

-- SQLite
SELECT 
  invoice_id,
  due_date,
  CAST(julianday('now') - julianday(due_date) AS INTEGER) as days_overdue
FROM invoices
WHERE due_date < date('now');

-- SQL Server - precise months
SELECT DATEDIFF(month, '2024-01-15', '2024-06-20') as months_diff;
-- Returns: 5

-- MySQL
SELECT TIMESTAMPDIFF(MONTH, '2024-01-15', '2024-06-20') as months_diff;
-- Returns: 5

-- PostgreSQL
SELECT EXTRACT(YEAR FROM AGE('2024-06-20', '2024-01-15')) * 12 +
       EXTRACT(MONTH FROM AGE('2024-06-20', '2024-01-15')) as months_diff;

-- SQLite (approximate - dividing days by 30)
SELECT CAST((julianday('2024-06-20') - julianday('2024-01-15')) / 30 AS INTEGER);
-- Returns: 5

-- SQL Server
SELECT DATEDIFF(year, '2020-03-15', '2024-06-20') as years_diff;

-- MySQL
SELECT TIMESTAMPDIFF(YEAR, '2020-03-15', '2024-06-20') as years_diff;

-- PostgreSQL
SELECT EXTRACT(YEAR FROM AGE('2024-06-20', '2020-03-15')) as years_diff;

-- SQLite (approximate)
SELECT CAST((julianday('2024-06-20') - julianday('2020-03-15')) / 365 AS INTEGER);

-- Find all events within 7 days of a target date
-- SQL Server
SELECT event_name, event_date
FROM events
WHERE ABS(DATEDIFF(day, event_date, '2024-04-15')) <= 7;

-- MySQL
SELECT event_name, event_date
FROM events
WHERE ABS(DATEDIFF(event_date, '2024-04-15')) <= 7;

-- SQLite
SELECT event_name, event_date
FROM events
WHERE ABS(julianday(event_date) - julianday('2024-04-15')) <= 7;

-- SQLite example
SELECT 
  customer_name,
  signup_date,
  CAST((julianday('now') - julianday(signup_date)) / 365 AS INTEGER) as years_with_us,
  CASE 
    WHEN julianday('now') - julianday(signup_date) < 365 THEN 'New'
    WHEN julianday('now') - julianday(signup_date) < 730 THEN 'Established'
    ELSE 'Loyal'
  END as customer_tier
FROM customers;

-- SQLite example
SELECT 
  ticket_id,
  created_at,
  resolved_at,
  CAST(julianday(resolved_at) - julianday(created_at) AS INTEGER) as resolution_days,
  CASE 
    WHEN julianday(resolved_at) - julianday(created_at) <= 3 THEN 'Within SLA'
    ELSE 'SLA Breach'
  END as sla_status
FROM support_tickets;

-- SQLite example - subscriptions expiring in next 30 days
SELECT 
  customer_name,
  subscription_end,
  CAST(julianday(subscription_end) - julianday('now') AS INTEGER) as days_remaining
FROM subscriptions
WHERE julianday(subscription_end) - julianday('now') BETWEEN 0 AND 30
ORDER BY days_remaining;

-- This returns NULL, not an error
SELECT julianday(NULL) - julianday('2024-01-01');

-- Handle NULLs explicitly
SELECT COALESCE(
  CAST(julianday(end_date) - julianday(start_date) AS INTEGER),
  0
) as duration_days
FROM projects;

-- SQLite expects ISO format: YYYY-MM-DD
-- This works:
SELECT julianday('2024-01-15');

-- This might not work as expected:
SELECT julianday('15-01-2024');
SELECT julianday('01/15/2024');

-- These are technically 0 days apart (same calendar day)
-- but the difference includes the time component
SELECT julianday('2024-01-15 23:59:59') - julianday('2024-01-15 00:00:00');
-- Returns: ~0.999988

-- Strip time for day-only calculations
SELECT julianday(date('2024-01-15 23:59:59')) - julianday(date('2024-01-15 00:00:00'));
-- Returns: 0

CREATE INDEX idx_orders_order_date ON orders(order_date);
CREATE INDEX idx_orders_ship_date ON orders(ship_date);

-- Bad: Function on column prevents index use
SELECT * FROM orders
WHERE julianday(order_date) > julianday('2024-01-01');

-- Better: Compare directly
SELECT * FROM orders
WHERE order_date > '2024-01-01';

-- Add a calculated column or separate table for frequently-needed values
ALTER TABLE orders ADD COLUMN fulfillment_days INTEGER;

UPDATE orders 
SET fulfillment_days = CAST(julianday(ship_date) - julianday(order_date) AS INTEGER)
WHERE ship_date IS NOT NULL;