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

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:

SELECT * FROM orders
WHERE order_date = CURRENT_DATE;

Or get entries from the past 7 days:

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:

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:

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:

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

SELECT CURRENT_DATE, CURRENT_TIME;

Or grab both together:

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

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

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

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

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

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 date
• CURRENT_TIME: Returns current time
• NOW(): Returns current date and time
• EXTRACT(part FROM date): Gets year, month, day, etc.
• DATE_TRUNC(): Trims timestamp to a specific unit
• DATEDIFF(): Difference between dates
• DATEADD(): Adds an interval to a date
• TO_CHAR(): Formats dates (PostgreSQL)
• FORMAT() or CONVERT(): 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:

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:

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

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

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:

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.

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

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.