Date Functions

Working with Dates

Date functions let you get the current date, perform date arithmetic, and extract parts of a date. Dialect naming varies more here than almost anywhere else in SQL.

CURRENT_DATE — Today's Date

sql
SELECT CURRENT_DATE;
-- e.g. 2026-09-14

CURRENT_DATE is standard SQL and widely supported (PostgreSQL, MySQL, SQLite). It returns today's date with no time component.

Adding/Subtracting Time — Dialect Variance

PostgreSQL uses INTERVAL arithmetic directly on dates:

sql
SELECT order_date + INTERVAL '7 days' AS week_later FROM orders WHERE id = 1;
-- 2024-01-05 + 7 days = 2024-01-12

MySQL uses DATE_ADD() / DATE_SUB():

sql
SELECT DATE_ADD(order_date, INTERVAL 7 DAY) AS week_later FROM orders WHERE id = 1;
-- 2024-01-12

SELECT DATE_SUB(order_date, INTERVAL 7 DAY) AS week_earlier FROM orders WHERE id = 1;
-- 2023-12-29

SQL Server uses DATEADD():

sql
SELECT DATEADD(day, 7, order_date) AS week_later FROM orders WHERE id = 1;
-- 2024-01-12

Three different spellings for the same operation — always check the target dialect's documentation before writing date arithmetic.

DATEDIFF — Difference Between Two Dates

sql
-- MySQL / SQL Server style
SELECT DATEDIFF(order_date, '2024-01-01') AS days_since_new_year FROM orders WHERE id = 1;
-- MySQL: 4 (2024-01-05 minus 2024-01-01)

Dialect variance: MySQL's DATEDIFF(date1, date2) returns date1 - date2 in days. SQL Server's DATEDIFF(unit, date1, date2) takes a unit (day, month, year, etc.) as the first argument and returns date2 - date1 — the argument order and meaning are reversed compared to MySQL, a genuine gotcha when porting code between engines. PostgreSQL has no built-in DATEDIFF; you simply subtract two dates directly (date2 - date1) to get an integer number of days.

EXTRACT — Pulling Out a Date Part

sql
SELECT EXTRACT(YEAR FROM order_date) AS order_year FROM orders WHERE id = 1;
-- 2024

SELECT EXTRACT(MONTH FROM order_date) AS order_month FROM orders WHERE id = 1;
-- 1

SELECT EXTRACT(DAY FROM order_date) AS order_day FROM orders WHERE id = 1;
-- 5

EXTRACT(part FROM date) is standard SQL, supported by PostgreSQL, MySQL, and Oracle. SQL Server instead commonly uses DATEPART(part, date) or YEAR()/MONTH()/DAY() convenience functions with equivalent results.

Practical Example: Orders in the Last 30 Days

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

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

Edge Cases

• Date arithmetic across month/year boundaries (e.g. adding a month to January 31st) can produce different results across engines — some clamp to the last valid day of the target month, others error.
• EXTRACT and date-arithmetic functions all propagate NULL: if order_date is NULL, every derived expression is also NULL.
• Time zones are not a concern for a plain DATE type (no time-of-day component at all) but become very relevant once TIMESTAMP/DATETIME is involved (covered next, in Time Functions).

Key Takeaways / Interview Q&A

Q: Name three different ways engines spell "add days to a date". A: PostgreSQL: date + INTERVAL 'n days'. MySQL: DATE_ADD(date, INTERVAL n DAY). SQL Server: DATEADD(day, n, date).

Q: What's the DATEDIFF argument-order gotcha between MySQL and SQL Server? A: MySQL's DATEDIFF(date1, date2) = date1 - date2; SQL Server's DATEDIFF(unit, date1, date2) = date2 - date1 (reversed) and additionally requires a unit as the first argument.

Q: What does EXTRACT(YEAR FROM order_date) do? A: Returns just the year component of a date as an integer, e.g. 2024.