Date Functions

Date Functions

SQL provides functions for working with dates and times.

Today's date and time

SELECT NOW();          -- current date and time (MySQL)
SELECT GETDATE();      -- current date and time (SQL Server)
SELECT CURDATE();      -- date only (MySQL)

Extract parts of a date

SELECT EXTRACT(YEAR FROM OrderDate) FROM Orders;   -- 2024
SELECT MONTH(OrderDate) FROM Orders;               -- 1..12 (MySQL)
SELECT YEAR(OrderDate) FROM Orders;                -- 2024

Add / subtract time

-- MySQL
SELECT DATE_ADD(OrderDate, INTERVAL 1 DAY) FROM Orders;
SELECT DATE_SUB(OrderDate, INTERVAL 2 MONTH) FROM Orders;

-- SQL Server
SELECT DATEADD(day, 1, OrderDate) FROM Orders;

Compare dates

Dates compare like strings in WHERE:

SELECT * FROM Orders
WHERE OrderDate > '2024-06-01';

Format a date

SELECT DATE_FORMAT(OrderDate, '%Y-%m-%d') FROM Orders;   -- MySQL

Note

The exact function names differ by database (NOW vs GETDATE, DATE_ADD vs DATEADD). Always check your database's docs.

TL;DR

• NOW / GETDATE return the current date and time.
• YEAR / MONTH / EXTRACT pull parts out of dates.
• DATE_ADD / DATEADD add time to a date.
• Dates can be compared with > and < in WHERE.