Date Functions

SQL provides functions for working with dates and times.

Today's date and time

sql

### 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

sql
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

sql - 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:

sql

### SELECT * FROM Orders

### WHERE OrderDate > '2024-06-01';

Format a date

sql
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.