Date Functions

-- Current date (no time):
SELECT CURDATE();           -- MySQL
SELECT CURRENT_DATE;        -- Standard SQL / PostgreSQL

-- Current date and time:
SELECT NOW();               -- MySQL
SELECT CURRENT_TIMESTAMP;   -- Standard SQL

Know both MySQL and PostgreSQL variants — interviews may not specify the database. NOW() includes time. CURDATE() / CURRENT_DATE is date only.

SELECT hire_date,
       YEAR(hire_date)  AS yr,
       MONTH(hire_date) AS mo,
       DAY(hire_date)   AS dy
FROM employees;

-- PostgreSQL:
SELECT EXTRACT(YEAR FROM hire_date) AS yr FROM employees;

YEAR(), MONTH(), DAY() are MySQL functions. PostgreSQL uses EXTRACT(). For cross-database SQL, EXTRACT is the standard way.

SELECT * FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

-- PostgreSQL:
SELECT * FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days';

DATE_SUB subtracts an interval from a date. This is the standard pattern for rolling window queries in dashboards and scheduled reports.

SELECT name, hire_date,
       DATEDIFF(CURDATE(), hire_date) AS days_employed
FROM employees;

-- PostgreSQL:
SELECT name, hire_date,
       CURRENT_DATE - hire_date AS days_employed
FROM employees;

DATEDIFF(end, start) returns the number of days between two dates. PostgreSQL allows direct date subtraction. Useful for tenure, SLA, and aging analysis.

-- First day of current month:
SELECT DATE_FORMAT(CURDATE(), '%Y-%m-01') AS first_day;

-- Last day of current month:
SELECT LAST_DAY(CURDATE()) AS last_day;

-- PostgreSQL:
SELECT DATE_TRUNC('month', CURRENT_DATE) AS first_day,
       DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month - 1 day' AS last_day;

These are essential for monthly report filters. LAST_DAY() in MySQL gives the last date of the month. DATE_TRUNC in PostgreSQL truncates to the start of a period.

SELECT WEEK(order_date) AS week_num,
       YEAR(order_date) AS yr,
       SUM(amount) AS weekly_revenue
FROM orders
GROUP BY YEAR(order_date), WEEK(order_date)
ORDER BY yr, week_num;

Always include YEAR in the GROUP BY when grouping by WEEK — otherwise week 1 of 2024 and week 1 of 2025 get merged. Common mistake in weekly trend reports.