Date Functions

Overview

Date functions allow you to perform chronological operations, extract date parts, calculate intervals, and handle timezone offsets.

Learning Objectives

• Retrieve active server date and time timestamps.
• Add or subtract date intervals with DATEADD and DATEDIFF.
• Truncate date granularity with DATE_TRUNC and extract parts with EXTRACT.

Detailed Concept Explanation

Dates are stored as specialized structures. SQL provides rich utility functions:

• CURRENT_DATE / CURRENT_TIMESTAMP: Gets active server system clock values.
• DATEDIFF(unit, start, end): Calculates chronological differences in years, months, or days.
• DATEADD(unit, interval, date): Adjusts date parameters by offsets.
• EXTRACT(part FROM date): Obtains single properties like year, month, or day.
• DATE_TRUNC(grain, date): Discards details below the specified grain, useful for month-over-month aggregations.

Code Examples

SQL

-- Calculate employee tenure in years
SELECT name, 
  DATEDIFF('year', hire_date, CURRENT_DATE) AS years_tenured
FROM employees;

-- Extract cohort year and month groupings
SELECT 
  EXTRACT(YEAR FROM signup_date) AS signup_year,
  DATE_TRUNC('month', signup_date) AS cohort_month,
  COUNT(*) AS user_count
FROM users
GROUP BY 1, 2;

Best Practices

• Avoid Filtering with Functions: Avoid writing WHERE EXTRACT(YEAR FROM order_date) = 2026 because it forces a full scan on order_date. Instead, write WHERE order_date BETWEEN '2026-01-01' AND '2026-12-31'.

Interview Perspective

Interactive Challenges

Summary

Date functions extract time attributes and calculate ranges, serving as critical tools in time-series and cohort analysis.