Date & Time Functions

Work with dates using YEAR, MONTH, DATE_ADD, DATEDIFF, and more.

18 min read
Beginner

Introduction to Date Functions

Dates and times are crucial in databases - order dates, employee hire dates, event timestamps, deadlines, and more. But working with dates can be tricky:

  • Calculate age from birthdate
  • Find records from last month
  • Extract year or day of week
  • Add or subtract days/months
  • Format dates for display

Date functions in SQL make these operations simple and powerful!

Built-in SQL functions that operate on DATE, DATETIME, and TIMESTAMP values. They extract parts of dates, perform calculations, format output, and handle time zones - essential for any time-based data analysis.

Why use date functions?

  • Calculate time differences (days between orders)
  • Filter by time periods (last quarter, this year)
  • Extract components (year, month, day, weekday)
  • Format dates for reports
  • Handle time zones
  • Schedule and track events

Let's work with an orders table:

orders
order_id
customer_name
order_date
ship_date
total
1Alice Smith2024-01-152024-01-18299.99
2Bob Johnson2024-02-202024-02-22150.00
3Carol White2024-03-102024-03-15450.50
4David Brown2024-03-252024-03-2889.99

CURRENT_DATE, CURRENT_TIME, NOW() - Current Date/Time

Get the current date and time from the database server:

sql
-- Get current date and time
SELECT
    CURRENT_DATE AS today,
    CURRENT_TIME AS right_now_time,
    NOW() AS right_now_datetime,
    CURRENT_TIMESTAMP AS timestamp_now;
sql
-- Find orders placed today
SELECT
    order_id,
    customer_name,
    order_date
FROM orders
WHERE order_date = CURRENT_DATE;

Use cases:

  • Record when records are created: created_at TIMESTAMP DEFAULT NOW()
  • Find recent activity
  • Calculate current age from birthdate

YEAR(), MONTH(), DAY() - Extracting Date Parts

Extract specific components from dates:

sql
-- Extract year, month, and day
SELECT
    order_id,
    order_date,
    YEAR(order_date) AS year,
    MONTH(order_date) AS month,
    DAY(order_date) AS day
FROM orders;
sql
-- Find orders from March 2024
SELECT
    order_id,
    customer_name,
    order_date
FROM orders
WHERE YEAR(order_date) = 2024
  AND MONTH(order_date) = 3;

Additional extraction functions:

  • HOUR(datetime) - extract hour (0-23)
  • MINUTE(datetime) - extract minute (0-59)
  • SECOND(datetime) - extract second (0-59)
  • QUARTER(date) - quarter of year (1-4)
  • WEEK(date) - week number of year

DAYNAME() and MONTHNAME() - Text Names

Get the name of the day or month:

sql
-- Get day and month names
SELECT
    order_id,
    order_date,
    DAYNAME(order_date) AS day_of_week,
    MONTHNAME(order_date) AS month_name
FROM orders;
sql
-- Find orders placed on weekends
SELECT
    order_id,
    customer_name,
    order_date,
    DAYNAME(order_date) AS day
FROM orders
WHERE DAYNAME(order_date) IN ('Saturday', 'Sunday');

Alternative: DAYOFWEEK() Returns a number (1=Sunday, 2=Monday, ..., 7=Saturday):

WHERE DAYOFWEEK(order_date) IN (1, 7) -- Weekends

DATE_ADD() and DATE_SUB() - Adding/Subtracting Time

Add or subtract intervals from dates:

Syntax:

DATE_ADD(date, INTERVAL value unit)
DATE_SUB(date, INTERVAL value unit)

Common units: DAY, MONTH, YEAR, HOUR, MINUTE, WEEK

sql
-- Add time to dates
SELECT
    order_id,
    order_date,
    DATE_ADD(order_date, INTERVAL 7 DAY) AS week_later,
    DATE_ADD(order_date, INTERVAL 1 MONTH) AS month_later,
    DATE_ADD(order_date, INTERVAL 1 YEAR) AS year_later
FROM orders
WHERE order_id = 1;
sql
-- Subtract time from dates
SELECT
    order_id,
    order_date,
    DATE_SUB(order_date, INTERVAL 30 DAY) AS thirty_days_ago,
    DATE_SUB(order_date, INTERVAL 3 MONTH) AS three_months_ago
FROM orders
WHERE order_id = 3;
sql
-- Find orders from the last 30 days
SELECT
    order_id,
    customer_name,
    order_date
FROM orders
WHERE order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY);

Alternative syntax (some databases):

order_date + INTERVAL 7 DAY
order_date - INTERVAL 1 MONTH

DATEDIFF() - Calculate Days Between Dates

Calculate the difference in days between two dates:

Syntax: DATEDIFF(date1, date2) Returns: date1 - date2 in days

sql
-- Calculate shipping time (days between order and ship)
SELECT
    order_id,
    customer_name,
    order_date,
    ship_date,
    DATEDIFF(ship_date, order_date) AS shipping_days
FROM orders;
sql
-- Calculate days since order
SELECT
    order_id,
    customer_name,
    order_date,
    DATEDIFF(CURRENT_DATE, order_date) AS days_since_order
FROM orders;
sql
-- Find orders with slow shipping (>3 days)
SELECT
    order_id,
    customer_name,
    DATEDIFF(ship_date, order_date) AS shipping_days
FROM orders
WHERE DATEDIFF(ship_date, order_date) > 3;

TIMESTAMPDIFF() - Difference in Any Unit

Calculate difference in specific units (days, months, years, hours):

Syntax: TIMESTAMPDIFF(unit, start_date, end_date)

Units: SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR

sql
-- Calculate age from birthdate
SELECT
    'Alice' AS name,
    '1990-05-15' AS birthdate,
    TIMESTAMPDIFF(YEAR, '1990-05-15', CURRENT_DATE) AS age_years,
    TIMESTAMPDIFF(MONTH, '1990-05-15', CURRENT_DATE) AS age_months,
    TIMESTAMPDIFF(DAY, '1990-05-15', CURRENT_DATE) AS age_days;
sql
-- Calculate months between dates
SELECT
    order_id,
    order_date,
    TIMESTAMPDIFF(MONTH, order_date, CURRENT_DATE) AS months_ago,
    TIMESTAMPDIFF(WEEK, order_date, CURRENT_DATE) AS weeks_ago
FROM orders;

DATE_FORMAT() - Formatting Dates

Format dates for display using format codes:

Common format codes:

  • %Y - 4-digit year (2024)
  • %y - 2-digit year (24)
  • %M - full month name (March)
  • %m - 2-digit month (03)
  • %D - day with suffix (15th)
  • %d - 2-digit day (15)
  • %W - weekday name (Monday)
  • %H - hour 24h (14)
  • %i - minutes (35)
sql
-- Format dates in various ways
SELECT
    order_id,
    order_date,
    DATE_FORMAT(order_date, '%M %d, %Y') AS format1,
    DATE_FORMAT(order_date, '%W, %D %M %Y') AS format2,
    DATE_FORMAT(order_date, '%m/%d/%y') AS format3,
    DATE_FORMAT(order_date, '%Y-%m-%d') AS iso_format
FROM orders;

Note: PostgreSQL uses TO_CHAR() instead:

TO_CHAR(order_date, 'Month DD, YYYY')

Common Mistakes to Avoid

Don't make these mistakes:

  1. Comparing dates with strings incorrectly: ❌

    • WHERE order_date = '15-01-2024' (wrong format)
    • Always use ISO format: '2024-01-15' (YYYY-MM-DD)

  2. Using functions in WHERE with indexes: ⚠️

    • WHERE YEAR(order_date) = 2024 can't use indexes efficiently
    • Better: WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'

  3. Forgetting about time zones: ⚠️

    • NOW() uses server timezone
    • Consider storing timestamps in UTC

  4. Wrong DATEDIFF argument order: ❌

    • DATEDIFF(date1, date2) returns date1 - date2
    • Be consistent with which is first

  5. Mixing DATE and DATETIME comparisons: ❌

    • WHERE datetime_col = '2024-01-15' won't match times other than 00:00:00
    • Use DATE(datetime_col) = '2024-01-15' or range comparison

  6. Not handling NULLs: ❌

    • Date functions on NULL return NULL
    • Check for NULL before applying functions

  7. Assuming all years have 365 days: ❌

    • Leap years have 366 days
    • Use DATE_ADD/DATE_SUB instead of manual calculations

Key Takeaways

What you learned:

✅ CURRENT_DATE, NOW() - get current date/time ✅ YEAR(), MONTH(), DAY() - extract date components ✅ DAYNAME(), MONTHNAME() - get text names ✅ DATE_ADD(), DATE_SUB() - add/subtract intervals ✅ DATEDIFF() - calculate days between dates ✅ TIMESTAMPDIFF() - calculate difference in any unit ✅ DATE_FORMAT() - format dates for display ✅ Date ranges with BETWEEN and comparisons

Real-world applications:

  • Calculate ages and tenure
  • Track time between events (order to ship)
  • Filter by time periods (last month, this quarter)
  • Format dates for reports
  • Identify trends by weekday/month
  • Schedule reminders (birthdays, renewals)

Practice Exercise: Using the orders table:

  1. Find all orders from the last 30 days
  2. Calculate average shipping days per month
  3. List orders placed on Mondays
  4. Find the oldest order and how many days ago it was
  5. Group orders by quarter and count them
  6. Format order dates as "Monday, March 25th, 2024"
  7. Find orders where shipping took more than 3 days
  8. Calculate how many months ago each order was placed
🗄️

SQL Fundamentals

Master SQL from basics to advanced topics. Learn to query, manipulate, and analyze data in relational databases.