SQL Date Functions Cheat Sheet

Reference for date manipulation, formatting, and calculations.

Current Date/Time

Syntax:

CURRENT_DATE | CURRENT_TIMESTAMP | NOW() | GETDATE()

Returns current date or datetime.

Example:

SELECT CURRENT_DATE, CURRENT_TIMESTAMP;

💡 Note: GETDATE() is SQL Server, NOW() is MySQL

Date Parts

Syntax:

EXTRACT(part FROM date) | YEAR(date) | MONTH(date) | DAY(date)

Extracts specific parts from a date.

Example:

SELECT EXTRACT(YEAR FROM order_date) as year, MONTH(order_date) as month FROM orders;

💡 Note: Parts: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND

Date Arithmetic

Syntax:

date + INTERVAL 'n unit' | DATEADD(unit, n, date)

Add or subtract intervals from dates.

Example:

SELECT order_date + INTERVAL '7 days' as due_date FROM orders;

💡 Note: DATEADD is SQL Server syntax

Date Difference

Syntax:

DATEDIFF(date1, date2) | date1 - date2

Calculates difference between dates.

Example:

SELECT DATEDIFF(end_date, start_date) as duration FROM projects;

💡 Note: SQL Server: DATEDIFF(unit, start, end)

DATE_TRUNC

Syntax:

DATE_TRUNC('unit', date)

Truncates date to specified precision.

Example:

SELECT DATE_TRUNC('month', order_date) as month FROM orders;

💡 Note: PostgreSQL. Use TRUNC or FORMAT in other DBs

Date Formatting

Syntax:

DATE_FORMAT(date, format) | TO_CHAR(date, format)

Formats date as string.

Example:

SELECT DATE_FORMAT(order_date, '%Y-%m-%d') as formatted FROM orders;

💡 Note: Format codes vary by database

Quick Reference Table

| Function | Purpose | |----------|---------| | Current Date/Time | Returns current date or datetime | | Date Parts | Extracts specific parts from a date | | Date Arithmetic | Add or subtract intervals from dates | | Date Difference | Calculates difference between dates | | DATE_TRUNC | Truncates date to specified precision | | Date Formatting | Formats date as string |

Related Cheat Sheets

Continue learning with more SQL references: