SQL - Date Functions Complete Reference
Date and time handling sits at the core of nearly every production database. Orders have timestamps. Users have birthdates. Subscriptions expire. Reports filter by date ranges. Get date functions...
Key Insights
- Date functions vary significantly across database systems—learn the syntax for your specific platform rather than memorizing generic SQL that won’t work anywhere.
- Always write sargable date queries by comparing columns directly against calculated values, not by wrapping indexed columns in functions.
- Store dates in proper date types and handle formatting at the application layer; databases should store data, not presentation logic.
Introduction to SQL Date Functions
Date and time handling sits at the core of nearly every production database. Orders have timestamps. Users have birthdates. Subscriptions expire. Reports filter by date ranges. Get date functions wrong, and you’ll spend hours debugging timezone issues, performance problems, and incorrect calculations.
SQL provides robust date functionality, but here’s the catch: every database system implements it differently. MySQL, PostgreSQL, SQL Server, and Oracle each have their own syntax, function names, and quirks. This reference covers the major patterns across these systems so you can translate between them.
The fundamental date types you’ll encounter:
- DATE: Calendar date only (2024-03-15)
- TIME: Time of day only (14:30:00)
- DATETIME/TIMESTAMP: Combined date and time (2024-03-15 14:30:00)
- TIMESTAMP WITH TIME ZONE: Includes timezone information (PostgreSQL, Oracle)
Getting Current Date and Time
Every database needs a way to capture “right now.” The function names differ, but the concept is universal.
-- MySQL
SELECT NOW(); -- 2024-03-15 14:30:45
SELECT CURRENT_DATE(); -- 2024-03-15
SELECT CURRENT_TIME(); -- 14:30:45
SELECT CURRENT_TIMESTAMP(); -- 2024-03-15 14:30:45
-- PostgreSQL
SELECT NOW(); -- 2024-03-15 14:30:45.123456-05
SELECT CURRENT_DATE; -- 2024-03-15
SELECT CURRENT_TIME; -- 14:30:45.123456-05
SELECT CURRENT_TIMESTAMP; -- 2024-03-15 14:30:45.123456-05
-- SQL Server
SELECT GETDATE(); -- 2024-03-15 14:30:45.123
SELECT SYSDATETIME(); -- 2024-03-15 14:30:45.1234567
SELECT CAST(GETDATE() AS DATE); -- 2024-03-15
-- Oracle
SELECT SYSDATE FROM DUAL; -- 15-MAR-24
SELECT SYSTIMESTAMP FROM DUAL; -- 15-MAR-24 02.30.45.123456 PM -05:00
Practical usage in an INSERT statement:
-- Recording when a user signed up (MySQL)
INSERT INTO users (email, created_at)
VALUES ('user@example.com', NOW());
-- SQL Server equivalent
INSERT INTO users (email, created_at)
VALUES ('user@example.com', GETDATE());
-- Using DEFAULT constraint is often cleaner
CREATE TABLE orders (
id INT PRIMARY KEY,
order_date DATETIME DEFAULT CURRENT_TIMESTAMP
);
Extracting Date Parts
Pulling specific components from dates—year, month, day, hour—is essential for grouping, filtering, and reporting.
-- MySQL: Individual functions
SELECT
YEAR(order_date) AS order_year,
MONTH(order_date) AS order_month,
DAY(order_date) AS order_day,
HOUR(created_at) AS created_hour
FROM orders;
-- PostgreSQL: EXTRACT function
SELECT
EXTRACT(YEAR FROM order_date) AS order_year,
EXTRACT(MONTH FROM order_date) AS order_month,
EXTRACT(DOW FROM order_date) AS day_of_week -- 0=Sunday
FROM orders;
-- SQL Server: DATEPART function
SELECT
DATEPART(YEAR, order_date) AS order_year,
DATEPART(MONTH, order_date) AS order_month,
DATEPART(WEEKDAY, order_date) AS day_of_week
FROM orders;
Grouping sales by month is a classic use case:
-- MySQL: Monthly revenue report
SELECT
YEAR(order_date) AS year,
MONTH(order_date) AS month,
SUM(total_amount) AS revenue,
COUNT(*) AS order_count
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER BY year, month;
-- PostgreSQL equivalent
SELECT
EXTRACT(YEAR FROM order_date) AS year,
EXTRACT(MONTH FROM order_date) AS month,
SUM(total_amount) AS revenue,
COUNT(*) AS order_count
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY 1, 2
ORDER BY 1, 2;
Filtering by day of week (finding all orders placed on weekends):
-- MySQL (1=Sunday, 7=Saturday)
SELECT * FROM orders
WHERE DAYOFWEEK(order_date) IN (1, 7);
-- PostgreSQL (0=Sunday, 6=Saturday)
SELECT * FROM orders
WHERE EXTRACT(DOW FROM order_date) IN (0, 6);
-- SQL Server (depends on DATEFIRST setting, typically 1=Sunday)
SELECT * FROM orders
WHERE DATEPART(WEEKDAY, order_date) IN (1, 7);
Date Arithmetic and Calculations
Adding and subtracting time from dates handles everything from calculating ages to finding records within a time window.
-- MySQL: DATE_ADD and DATE_SUB
SELECT DATE_ADD(NOW(), INTERVAL 30 DAY); -- 30 days from now
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH); -- 1 month ago
SELECT DATE_ADD(NOW(), INTERVAL 2 HOUR); -- 2 hours from now
-- PostgreSQL: INTERVAL syntax
SELECT NOW() + INTERVAL '30 days';
SELECT NOW() - INTERVAL '1 month';
SELECT created_at + INTERVAL '1 year' AS renewal_date FROM subscriptions;
-- SQL Server: DATEADD
SELECT DATEADD(DAY, 30, GETDATE()); -- 30 days from now
SELECT DATEADD(MONTH, -1, GETDATE()); -- 1 month ago
SELECT DATEADD(YEAR, 1, start_date) AS end_date FROM subscriptions;
Calculating the difference between dates:
-- MySQL: DATEDIFF returns days
SELECT DATEDIFF(NOW(), created_at) AS days_since_signup
FROM users;
-- PostgreSQL: Subtract dates directly
SELECT (CURRENT_DATE - created_at::DATE) AS days_since_signup
FROM users;
-- SQL Server: DATEDIFF with unit specification
SELECT DATEDIFF(DAY, created_at, GETDATE()) AS days_since_signup
FROM users;
SELECT DATEDIFF(YEAR, birthdate, GETDATE()) AS age
FROM users;
Finding records from the last 30 days (a query you’ll write constantly):
-- MySQL
SELECT * FROM orders
WHERE order_date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY);
-- PostgreSQL
SELECT * FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days';
-- SQL Server
SELECT * FROM orders
WHERE order_date >= DATEADD(DAY, -30, CAST(GETDATE() AS DATE));
Formatting and Converting Dates
Formatting dates for display and converting strings to dates are necessary evils. Do this at the application layer when possible, but sometimes SQL is the right place.
-- MySQL: DATE_FORMAT
SELECT DATE_FORMAT(order_date, '%m/%d/%Y') AS formatted_date FROM orders;
SELECT DATE_FORMAT(created_at, '%W, %M %d, %Y') AS long_date FROM users;
-- Output: "Friday, March 15, 2024"
-- PostgreSQL: TO_CHAR
SELECT TO_CHAR(order_date, 'MM/DD/YYYY') AS formatted_date FROM orders;
SELECT TO_CHAR(created_at, 'Day, Month DD, YYYY') AS long_date FROM users;
-- SQL Server: FORMAT (SQL Server 2012+)
SELECT FORMAT(order_date, 'MM/dd/yyyy') AS formatted_date FROM orders;
SELECT FORMAT(order_date, 'D', 'en-US') AS long_date FROM orders;
-- Legacy: CONVERT with style codes
SELECT CONVERT(VARCHAR, order_date, 101) AS formatted_date FROM orders; -- MM/DD/YYYY
Converting strings to dates:
-- MySQL: STR_TO_DATE
SELECT STR_TO_DATE('03/15/2024', '%m/%d/%Y');
INSERT INTO events (event_date)
VALUES (STR_TO_DATE('March 15, 2024', '%M %d, %Y'));
-- PostgreSQL: TO_DATE or CAST
SELECT TO_DATE('03/15/2024', 'MM/DD/YYYY');
SELECT '2024-03-15'::DATE;
-- SQL Server: CONVERT or CAST
SELECT CONVERT(DATE, '03/15/2024', 101);
SELECT CAST('2024-03-15' AS DATE);
Practical Use Cases and Patterns
Real-world scenarios require combining these functions. Here are patterns you’ll use repeatedly.
Monthly report with date range:
-- PostgreSQL: First and last day of current month
SELECT
DATE_TRUNC('month', CURRENT_DATE) AS month_start,
(DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month' - INTERVAL '1 day') AS month_end;
-- Full monthly report
SELECT
DATE_TRUNC('month', order_date) AS month,
COUNT(*) AS orders,
SUM(total_amount) AS revenue
FROM orders
WHERE order_date >= DATE_TRUNC('year', CURRENT_DATE)
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;
Fiscal year grouping (assuming fiscal year starts in April):
-- SQL Server
SELECT
CASE
WHEN MONTH(order_date) >= 4 THEN YEAR(order_date)
ELSE YEAR(order_date) - 1
END AS fiscal_year,
SUM(total_amount) AS revenue
FROM orders
GROUP BY CASE
WHEN MONTH(order_date) >= 4 THEN YEAR(order_date)
ELSE YEAR(order_date) - 1
END;
Timezone conversion (PostgreSQL):
-- Convert UTC timestamps to local time
SELECT
created_at AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York' AS local_time
FROM events;
Performance Considerations and Best Practices
Date queries can destroy performance if written carelessly. The critical concept is sargability—whether the query can use an index.
Non-sargable (bad): Function on the indexed column forces a full table scan.
-- DON'T DO THIS
SELECT * FROM orders
WHERE YEAR(order_date) = 2024;
SELECT * FROM orders
WHERE DATE(created_at) = '2024-03-15';
Sargable (good): Compare the column directly against calculated values.
-- DO THIS INSTEAD
SELECT * FROM orders
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';
SELECT * FROM orders
WHERE created_at >= '2024-03-15' AND created_at < '2024-03-16';
Optimized date range pattern:
-- MySQL: Finding orders from last 30 days (sargable)
SELECT * FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL 30 DAY;
-- The function is on the right side, not on the indexed column
-- This allows the index on order_date to be used
Index your date columns that appear in WHERE clauses and JOIN conditions. For tables with millions of rows, a missing date index can turn a 50ms query into a 50-second disaster.
CREATE INDEX idx_orders_order_date ON orders(order_date);
CREATE INDEX idx_users_created_at ON users(created_at);
Store dates properly. Don’t store dates as strings. Don’t store Unix timestamps when your database has proper date types. The minor convenience isn’t worth the query complexity and performance hit.
Date functions are foundational SQL knowledge. Master your database’s specific syntax, write sargable queries, and index appropriately. The rest is just looking up format strings when you need them.