SQL Date Functions: Syntax, Usage, and Examples
SQL date functions let you work with dates and times in queries. Whether you’re filtering by a specific date, calculating age, or formatting a timestamp, date functions in SQL help you extract, transform, and manipulate temporal data.
How to Use SQL Date Functions
You use SQL date functions inside SELECT, WHERE, ORDER BY, and other clauses to work with date values. These functions vary slightly between SQL dialects (like MySQL, PostgreSQL, or SQL Server), but the basic ideas remain consistent.
Common Syntax Examples
sql
SELECT CURRENT_DATE; -- Returns today's date
SELECT NOW(); -- Returns the current date and time
SELECT DATEPART(year, '2024-04-14'); -- Extracts the year
SELECT DATEDIFF(day, '2024-01-01', '2024-04-14'); -- Calculates difference
You can also use CAST, CONVERT, or formatting functions depending on the database.
When to Use SQL Date Functions
You’ll often use SQL date functions when working with timestamps, scheduling systems, reporting dashboards, or anything involving time-based logic.
Filter Records by Date
To pull records from a specific date or date range:
sql
SELECT * FROM orders
WHERE order_date = CURRENT_DATE;
Or get entries from the past 7 days:
sql
SELECT * FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '7 days';
This kind of query is common in dashboards or data summaries.
Extract Parts of a Date
You might need just the year, month, or day:
sql
SELECT EXTRACT(MONTH FROM order_date) AS order_month
FROM orders;
You can group or filter based on extracted components.
Calculate Age or Duration
You can use date difference functions to get durations:
sql
SELECT DATEDIFF(year, birth_date, CURRENT_DATE) AS age
FROM users;
This is useful for user profiles, subscription periods, or project timelines.
Format Output for Reports
Sometimes, the raw date isn’t presentation-friendly. Use formatting functions to clean it up:
sql
SELECT TO_CHAR(order_date, 'MM/DD/YYYY') AS formatted_date
FROM orders;
You can also combine this with other fields to create readable summaries.
Examples of SQL Date Functions in Practice
Let’s walk through some practical scenarios using date functions SQL developers rely on every day.
Get Today’s Date and Time
sql
SELECT CURRENT_DATE, CURRENT_TIME;
Or grab both together:
sql
SELECT NOW(); -- Timestamp in PostgreSQL or MySQL
This is handy for time-stamping operations or logging.
Use a Date Function in SQL Query to Filter Records
sql
SELECT * FROM users
WHERE signup_date >= CURRENT_DATE - INTERVAL '30 days';
You’re pulling recent signups in the last month.
Calculate Days Between Two Dates
sql
SELECT name,
DATEDIFF(day, start_date, end_date) AS duration
FROM projects;
You’ll often use this to monitor task progress or report deadlines.
Truncate Dates to a Specific Granularity
sql
SELECT DATE_TRUNC('month', created_at) AS month_bucket, COUNT(*)
FROM sales
GROUP BY month_bucket;
You’re grouping all sales data by month, regardless of the exact day.
Add or Subtract Time
sql
SELECT NOW() + INTERVAL '3 days'; -- Future date
SELECT NOW() - INTERVAL '1 week'; -- One week ago
Great for creating reminders, expiration dates, or future scheduling.
Use Date Functions in WHERE Clauses
sql
SELECT * FROM logs
WHERE DATE(created_at) = CURRENT_DATE;
By converting created_at to a pure date, you ignore the time component for cleaner filtering.
Learn More About SQL Date Functions
Popular SQL Date Functions Across Dialects
Here are some of the most commonly used ones:
CURRENT_DATE: Returns today’s dateCURRENT_TIME: Returns current timeNOW(): Returns current date and timeEXTRACT(part FROM date): Gets year, month, day, etc.DATE_TRUNC(): Trims timestamp to a specific unitDATEDIFF(): Difference between datesDATEADD(): Adds an interval to a dateTO_CHAR(): Formats dates (PostgreSQL)FORMAT()orCONVERT(): Formats or casts dates (MySQL/SQL Server)
These cover most date-time operations you’ll need.
Handle Date Functions in SQL with Time Zones
Some databases return time in UTC, while others respect server-local settings. If you’re working across time zones, explicitly convert your values:
sql
SELECT CURRENT_TIMESTAMP AT TIME ZONE 'UTC';
Always make sure timestamps match your expected time zone—especially in analytics.
Date Time Functions in SQL for Aggregates
Date functions pair perfectly with GROUP BY:
sql
SELECT EXTRACT(YEAR FROM order_date) AS year,
COUNT(*) AS orders_count
FROM orders
GROUP BY year
ORDER BY year;
You can easily produce annual reports, trend graphs, or dashboard stats this way.
Combine SQL Date Functions with CASE
sql
SELECT name,
CASE
WHEN DATEDIFF(day, last_login, CURRENT_DATE) > 30 THEN 'Inactive'
ELSE 'Active'
END AS status
FROM users;
This lets you categorize users based on how recently they logged in.
Use SQL Date Functions in Subqueries
sql
SELECT * FROM events
WHERE start_date = (
SELECT MAX(start_date)
FROM events
);
This finds the most recent event.
You can also pair dates with window functions to get rankings or gaps in data.
SQL Query for Between Dates
You’ll often use BETWEEN with date ranges:
sql
SELECT * FROM bookings
WHERE booking_date BETWEEN '2024-04-01' AND '2024-04-14';
Just make sure to use consistent date formats across your system.
Function Date in SQL for Cleaning Data
Some data might contain extra time values you don’t need. Use CAST() or DATE() to isolate the date.
sql
SELECT DATE(updated_at) FROM records;
This strips out the time, which helps with clean grouping or visual display.
Combine with Joins and Other Clauses
sql
SELECT users.name, orders.total, orders.created_at
FROM users
JOIN orders ON users.id = orders.user_id
WHERE DATE(orders.created_at) >= CURRENT_DATE - INTERVAL '7 days';
You’re joining tables but still filtering by recent dates. This shows how flexible SQL date functions can be in real-world queries.