Date & Time Functions

Working with dates and times is one of the trickiest corners of SQL precisely because so little of it is standardized in practice. The concepts — getting the current time, pulling out a year or month, adding an interval, formatting for display — are the same everywhere, but almost every function name and every bit of syntax around intervals differs from one database to the next.

Getting the current date/time

Function	Returns	Notes
`CURRENT_DATE`	Today's date, no time component	ANSI standard, widely supported
`CURRENT_TIMESTAMP`	Current date and time	ANSI standard, widely supported
`NOW()`	Current date and time	Common alias in PostgreSQL and MySQL; not standard SQL Server syntax
`GETDATE()`	Current date and time	SQL Server specific

Current date and time

SQL

SELECT CURRENT_DATE;        -- e.g. 2026-07-08
SELECT CURRENT_TIMESTAMP;   -- e.g. 2026-07-08 14:32:07
SELECT NOW();                -- PostgreSQL / MySQL alias for CURRENT_TIMESTAMP

Extracting parts of a date

The ANSI-standard way to pull a piece out of a date is EXTRACT(field FROM source). MySQL also offers dedicated shorthand functions for the most common fields.

Goal	ANSI standard / PostgreSQL	MySQL shorthand
Year	`EXTRACT(YEAR FROM order_date)`	`YEAR(order_date)`
Month	`EXTRACT(MONTH FROM order_date)`	`MONTH(order_date)`
Day	`EXTRACT(DAY FROM order_date)`	`DAY(order_date)`

Extracting the year from a date

SQL

-- ANSI standard (PostgreSQL, Oracle, SQL Server all support EXTRACT)
SELECT EXTRACT(YEAR FROM order_date) AS order_year FROM orders;

-- MySQL shorthand
SELECT YEAR(order_date) AS order_year FROM orders;

Date arithmetic

Adding or subtracting a span of time from a date is one of the most dialect-specific areas in all of SQL — the syntax below all accomplishes the same thing, "7 days from now", in four different ways:

Adding 7 days to the current date (dialect-specific)

SQL

-- PostgreSQL
SELECT CURRENT_DATE + INTERVAL '7 days';

-- MySQL
SELECT DATE_ADD(CURRENT_DATE, INTERVAL 7 DAY);

-- SQL Server
SELECT DATEADD(day, 7, GETDATE());

-- Oracle
SELECT SYSDATE + 7 FROM dual;

Interval syntax is not portable

There is no single ANSI form of interval arithmetic that works unchanged across all major databases. If you write code that needs to run on more than one database engine, isolate date-arithmetic expressions so they are easy to swap per platform.

Formatting dates for display

Database	Function	Example
PostgreSQL / Oracle	`TO_CHAR(date, format)`	`TO_CHAR(order_date, 'YYYY-MM-DD')`
MySQL	`DATE_FORMAT(date, format)`	`DATE_FORMAT(order_date, '%Y-%m-%d')`
SQL Server	`FORMAT(date, format)`	`FORMAT(order_date, 'yyyy-MM-dd')`

Formatting a date as YYYY-MM-DD (dialect-specific)

SQL

-- PostgreSQL / Oracle
SELECT TO_CHAR(order_date, 'YYYY-MM-DD') AS formatted_date FROM orders;

-- MySQL
SELECT DATE_FORMAT(order_date, '%Y-%m-%d') AS formatted_date FROM orders;

-- SQL Server
SELECT FORMAT(order_date, 'yyyy-MM-dd') AS formatted_date FROM orders;

Worked example — days between two dates and age

Subtracting one date from another gives you a number of days in most databases, which is the basis for both "days between two dates" and "age from a birthdate" calculations:

Days between two dates and calculating age

SQL

-- Days between order and shipment (PostgreSQL / MySQL / SQL Server all support subtraction)
SELECT shipped_date - order_date AS days_to_ship
FROM orders;

-- Age in whole years from a birthdate (PostgreSQL)
SELECT DATE_PART('year', AGE(CURRENT_DATE, birthdate)) AS age_years
FROM customers;

-- Age in whole years (portable approximation using EXTRACT)
SELECT EXTRACT(YEAR FROM AGE(CURRENT_DATE, birthdate)) AS age_years
FROM customers;