Date Functions

Intermediate

⏱️ 12 mins read

What You'll Learn

Date functions are critical in analytics — most business queries involve time periods, age calculations, or date formatting. Key functions: NOW()/CURRENT_DATE (current time), YEAR/MONTH/DAY (extract parts), DATE_DIFF (difference between dates), DATE_ADD/DATE_SUB (arithmetic), DATE_FORMAT/TO_CHAR (formatting).

Syntax

NOW() / CURRENT_DATE()
YEAR(date) / MONTH(date) / DAY(date)
DATEDIFF(date1, date2)
DATE_ADD(date, INTERVAL n unit)
DATE_FORMAT(date, format)

Example

-- Current date/time
SELECT NOW(), CURRENT_DATE();

-- Extract date parts
SELECT
  YEAR(order_date)    AS yr,
  MONTH(order_date)   AS mo,
  DAYNAME(order_date) AS weekday
FROM orders;

-- Date arithmetic
SELECT
  name,
  DATEDIFF(NOW(), hire_date)        AS days_employed,
  DATE_ADD(order_date, INTERVAL 30 DAY) AS due_date
FROM employees;

-- Group by month
SELECT DATE_FORMAT(order_date, '%Y-%m') AS month,
       SUM(total) AS revenue
FROM orders
GROUP BY month;

Common Mistakes

Applying functions to indexed date columns in WHERE (e.g., YEAR(order_date) = 2024) prevents index usage. Use range conditions instead: WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31'.

Interview Tips

Know how to group by week, month, quarter. Know how to calculate age (DATEDIFF in years). PostgreSQL uses EXTRACT and DATE_TRUNC. SQL Server uses DATEPART and DATEDIFF with different argument order.

Practice

Find all customers who signed up in the last 90 days. Count how many signed up per week. Also find the average days between a customer's first and second order.

Back to Full Roadmap