SQL Date Functions Guide

SQL date functions are some of the most important functions in practical database work because time is involved in almost every real system.

Applications track things like:

• user signups
• orders
• invoices
• sessions
• subscriptions
• support tickets
• payments
• shipments
• logs
• and analytics events

That means SQL developers constantly need to answer questions like:

• how many users signed up today?
• what was revenue last month?
• which orders are overdue?
• how many tickets were opened in the last 7 days?
• what is the average time between signup and first purchase?
• how do we group data by day, week, or month?

All of those problems depend on date and time logic.

The challenge is that SQL date functions are one of the places where database engines differ the most. The core ideas are stable, but the exact syntax often changes between:

• PostgreSQL
• MySQL
• SQL Server
• SQLite
• and other engines

That is why the best way to learn SQL date functions is not to memorize one giant list of engine-specific syntax. It is to understand the core patterns clearly, then adapt them to your database.

This guide covers those patterns in a practical way.

Why SQL date functions matter so much

Date logic shows up in almost every serious SQL workload.

For example:

In backend development

You need to:

• filter recent records
• expire sessions
• schedule jobs
• mark overdue invoices
• or fetch the newest items first

In analytics

You need to:

• group by day or month
• calculate week-over-week growth
• analyze churn windows
• build retention reports
• and track time-based trends

In operations

You need to:

• find unresolved tickets older than 48 hours
• track delivery windows
• monitor aging inventory
• and measure turnaround time

That is why date functions are not a niche SQL topic. They are part of everyday database work.

The most important rule

Before learning specific functions, remember this:

The most important date skill in SQL is knowing how to filter and group time data correctly, not just memorizing function names.

That matters because a lot of SQL date bugs happen even when the syntax is technically valid.

Common real problems include:

• filtering by month in a way that misses time values
• comparing dates and timestamps incorrectly
• grouping by formatted strings instead of proper date logic
• wrapping indexed date columns in functions and making the query slow
• confusing local time with UTC
• using inclusive end dates incorrectly

So while function syntax matters, the real value is understanding the patterns.

Dates, times, timestamps, and intervals

Before using date functions, it helps to understand the common types you work with.

Different databases use slightly different names, but the main ideas are usually:

DATE

Stores a calendar date only.

Example:

• 2026-04-04

TIME

Stores a time only.

Example:

• 14:30:00

DATETIME or TIMESTAMP

Stores both date and time.

Example:

• 2026-04-04 14:30:00

TIMESTAMP WITH TIME ZONE or equivalent behavior

Stores date and time with timezone-aware semantics, depending on the database.

INTERVAL or duration-style value

Represents a span of time such as:

• 7 days
• 2 hours
• 1 month

These distinctions matter because:

• filtering a DATE column
• and filtering a TIMESTAMP column

often require slightly different thinking.

Current date and current time

One of the first things developers need is the current date or current timestamp.

The exact syntax depends on the database, but the general ideas are common.

Current date

Conceptual pattern:

SELECT CURRENT_DATE;

This returns today’s date.

Current timestamp

Conceptual pattern:

SELECT CURRENT_TIMESTAMP;

This returns the current date and time.

Some engines also support patterns like:

• NOW()
• engine-specific date-time functions
• system date functions

The core point is:

• current date means date only
• current timestamp means date plus time

That difference matters when writing filters.

Why CURRENT_DATE and CURRENT_TIMESTAMP are different

Suppose you are filtering a timestamp column called created_at.

This query:

SELECT *
FROM orders
WHERE created_at = CURRENT_DATE;

often does not do what people expect.

Why?

Because:

• CURRENT_DATE usually means midnight of today or a pure date value
• created_at includes time

So a row with:

• 2026-04-04 15:32:00

may not equal:

• 2026-04-04

directly.

That is why date filtering usually works better with ranges rather than equality on timestamp columns.

Filtering rows from today

A safer pattern for timestamp columns is usually:

SELECT *
FROM orders
WHERE created_at >= CURRENT_DATE
  AND created_at < CURRENT_DATE + INTERVAL '1 day';

The exact interval syntax may vary by database, but the idea is strong across engines:

• start at the beginning of today
• stop before the beginning of tomorrow

This avoids mistakes with time values.

Why range filtering is better than date extraction in WHERE

A lot of beginners write queries like:

WHERE YEAR(created_at) = 2026
  AND MONTH(created_at) = 4

or:

WHERE DATE(created_at) = CURRENT_DATE

These can work logically, but they often have a performance downside: they wrap the column in a function.

That can make it harder for the database to use an index efficiently on the raw date or timestamp column.

A better pattern is often:

WHERE created_at >= '2026-04-01'
  AND created_at < '2026-05-01'

This is one of the most important real-world date-query habits.

It is:

• clear
• correct
• and often more index-friendly

Date arithmetic

Date arithmetic means adding or subtracting time.

This is used constantly for:

• last 7 days
• next 30 days
• overdue after 14 days
• retention windows
• expiration checks
• rolling reports

The exact syntax differs by database, but the general pattern is always:

• add or subtract a time interval

Example concept: last 7 days

SELECT *
FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '7 days';

Example concept: next 30 days

SELECT *
FROM subscriptions
WHERE renewal_date < CURRENT_DATE + INTERVAL '30 days';

These are common reporting and application patterns.

Common date arithmetic use cases

Date arithmetic is used for questions like:

• orders in the last 24 hours
• invoices due in the next 14 days
• tickets older than 48 hours
• sessions active in the last 30 minutes
• users who signed up in the last 90 days
• events between one date and another

That is why learning interval-style date logic is so important.

Extracting parts of a date

A very common need is extracting:

• year
• month
• day
• hour
• weekday
• quarter

This is useful when building summaries or labels.

Typical conceptual examples include:

SELECT
    EXTRACT(YEAR FROM created_at) AS year_value,
    EXTRACT(MONTH FROM created_at) AS month_value
FROM orders;

Different databases may use:

• EXTRACT
• YEAR(...)
• MONTH(...)
• DATEPART(...)
• or other engine-specific syntax

The important idea is the same: you are breaking a date or timestamp into useful components.

Example: group orders by month

One common approach is:

SELECT
    EXTRACT(YEAR FROM created_at) AS order_year,
    EXTRACT(MONTH FROM created_at) AS order_month,
    COUNT(*) AS order_count
FROM orders
GROUP BY
    EXTRACT(YEAR FROM created_at),
    EXTRACT(MONTH FROM created_at)
ORDER BY order_year, order_month;

This works conceptually across many systems, though function names differ.

It is useful for:

• monthly reporting
• trend analysis
• dashboards
• seasonal comparisons

Date truncation

Another important pattern is truncating a timestamp to a larger unit such as:

• day
• week
• month
• year

This is especially common in analytics and reporting.

Example concept:

SELECT
    DATE_TRUNC('month', created_at) AS month_start,
    COUNT(*) AS order_count
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month_start;

The exact function name varies by engine, but the idea is powerful: instead of extracting parts separately, you normalize each timestamp to the start of the time bucket.

This is often a very clean way to group time-based data.

Grouping by day, week, month, and year

This is one of the most common date-function tasks.

Group by day

Useful for:

• daily orders
• daily signups
• daily ticket volume
• daily revenue

Group by week

Useful for:

• operational trend reports
• weekly cohorts
• weekly performance summaries

Group by month

Useful for:

• monthly revenue
• monthly active users
• monthly churn
• monthly billing analysis

Group by year

Useful for:

• yearly trends
• long-term growth reports
• annual comparisons

The exact syntax depends on your engine, but the key design question is:

• what time bucket does each output row represent?

Once that is clear, the right grouping pattern becomes much easier.

Date difference functions

Another common requirement is calculating the difference between two dates.

Examples:

• days between signup and first purchase
• hours between ticket open and resolution
• days overdue
• months of customer tenure
• shipping delay between order and delivery

Different databases use different functions here, but conceptually you are asking:

• what is the duration between point A and point B?

Example concept:

SELECT
    order_id,
    shipped_at - ordered_at AS shipping_duration
FROM orders;

Or with engine-specific date difference functions.

The important point is that date differences are often used to measure:

• time to resolution
• time to convert
• time to deliver
• time since last activity
• age of records

That makes them especially valuable in operations and analytics.

Example: overdue invoices

Conceptual example:

SELECT
    invoice_id,
    due_date,
    CURRENT_DATE - due_date AS days_overdue
FROM invoices
WHERE due_date < CURRENT_DATE
  AND paid_date IS NULL;

This kind of query is very common in:

• finance systems
• subscription billing
• operational alerts
• customer success workflows

Formatting dates for display

Formatting dates is another common requirement, especially in:

• reports
• exports
• dashboards
• CSV files
• application-facing query results

Examples of formatting might include:

• 2026-04-04
• 04/04/2026
• April 2026
• 2026-Q2

Different databases use different functions for formatting, and some are much stronger than others here.

But there is an important practical rule:

Formatting is usually better handled at the presentation layer unless the SQL result specifically needs the formatted string.

Why?

Because formatted strings:

• are harder to sort correctly than real date values
• are not ideal for date arithmetic
• are less reusable
• and can make queries more presentation-heavy than necessary

So use formatting when you need a display string, but try not to replace real date logic with formatting too early.

Filtering by date ranges safely

This is one of the highest-value SQL date skills.

Suppose you want all orders in April 2026.

A strong pattern is:

SELECT *
FROM orders
WHERE created_at >= '2026-04-01'
  AND created_at < '2026-05-01';

This is usually better than:

WHERE created_at BETWEEN '2026-04-01' AND '2026-04-30'

Why?

Because timestamps often include times. If you write:

• 2026-04-30

you may unintentionally exclude rows later on that date depending on the engine and data type.

The inclusive-lower-bound and exclusive-upper-bound pattern is one of the safest date-filtering habits you can build.

Why inclusive lower bound and exclusive upper bound is so useful

This pattern:

created_at >= start_value
AND created_at < end_value

works very well because it:

• avoids time-of-day mistakes
• handles timestamp precision cleanly
• chains naturally from one period to the next
• makes month and day boundaries easier to reason about

It is widely useful for:

• days
• weeks
• months
• years
• rolling windows
• billing periods
• analytics buckets

This is one of the most reusable SQL date patterns in real work.

Common date query patterns

Here are some of the most common date-driven SQL tasks developers and analysts face.

Rows from the last 7 days

Conceptually:

SELECT *
FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '7 days';

Useful for:

• recent activity
• short operational windows
• alerting
• dashboard summaries

Rows from this month

Conceptually:

SELECT *
FROM orders
WHERE created_at >= DATE_TRUNC('month', CURRENT_DATE)
  AND created_at < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month';

The exact syntax varies, but the logic is:

• start of this month
• up to but not including next month

Rows from today

For timestamps, usually use a day range rather than direct equality:

SELECT *
FROM orders
WHERE created_at >= CURRENT_DATE
  AND created_at < CURRENT_DATE + INTERVAL '1 day';

Upcoming deadlines

SELECT *
FROM tasks
WHERE due_date >= CURRENT_DATE
  AND due_date < CURRENT_DATE + INTERVAL '7 days';

Useful for:

• task reminders
• renewals
• subscription expirations
• delivery windows

Overdue items

SELECT *
FROM invoices
WHERE due_date < CURRENT_DATE
  AND paid_date IS NULL;

One of the most common operational date filters.

Date functions with GROUP BY and aggregates

A lot of date work becomes powerful when combined with aggregates.

Example concepts:

• signups by month
• revenue by week
• tickets opened per day
• average delivery time by month
• failed payments by quarter

Example: daily order count

SELECT
    DATE(created_at) AS order_day,
    COUNT(*) AS order_count
FROM orders
GROUP BY DATE(created_at)
ORDER BY order_day;

This exact syntax varies, but the idea is:

• normalize each timestamp to the day level
• then count rows by day

Example: monthly revenue

SELECT
    DATE_TRUNC('month', created_at) AS revenue_month,
    SUM(total_amount) AS total_revenue
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY revenue_month;

This is one of the most common SQL reporting patterns.

Date functions with CASE

Date logic often combines naturally with CASE.

Example: aging categories.

SELECT
    invoice_id,
    due_date,
    CASE
        WHEN due_date < CURRENT_DATE - INTERVAL '30 days' THEN '30+ Days Overdue'
        WHEN due_date < CURRENT_DATE THEN 'Overdue'
        ELSE 'Current'
    END AS aging_bucket
FROM invoices;

This is useful for:

• accounts receivable
• support tickets
• task management
• backlog reports
• SLA analysis

Date-based categorization is one of the most practical combinations of CASE and date logic.

Time zones and SQL date handling

Time zones are one of the biggest sources of confusion in SQL date work.

A few practical rules help a lot:

1. Know whether your column stores date only or timestamp

These behave differently.

2. Know whether timestamps are stored in UTC or local time

This affects reporting and boundaries.

3. Be careful when using CURRENT_DATE or CURRENT_TIMESTAMP

Their meaning depends on the database session timezone.

4. Be explicit in systems with global users

If the business uses UTC, keep reporting logic consistent with UTC or convert clearly where needed.

A lot of “wrong date” bugs are really timezone bugs.

Common mistakes with SQL date functions

There are a few date-function mistakes that cause problems constantly.

1. Comparing timestamps directly to dates with equality

Bad pattern:

WHERE created_at = CURRENT_DATE

This often misses rows because the timestamp includes time.

Better pattern:

WHERE created_at >= CURRENT_DATE
  AND created_at < CURRENT_DATE + INTERVAL '1 day'

2. Wrapping indexed date columns in functions inside WHERE

Example:

WHERE DATE(created_at) = CURRENT_DATE

This may work logically, but it can reduce index efficiency.

Range filters are often better.

3. Using BETWEEN carelessly with timestamps

For full timestamp periods, an exclusive upper bound is usually safer than trying to guess the last possible time of the day.

4. Forgetting timezone context

A row can appear to belong to a different day depending on timezone interpretation.

5. Grouping by formatted strings too early

Formatting is useful for output, but raw date truncation or date buckets are usually better for accurate grouping and sorting.

6. Assuming date functions are portable without checking the database engine

The concepts are portable. The syntax often is not.

This is especially true for:

• date truncation
• date differences
• interval syntax
• formatting
• weekday logic
• week numbering

Performance tips for date queries

Date queries can become expensive on large tables, especially when the table is time-based and growing continuously.

Here are the most useful habits.

Use range filters on raw columns where possible

Example:

WHERE created_at >= '2026-04-01'
  AND created_at < '2026-05-01'

This is often better than extracting month and year in WHERE.

Index important date columns

Especially if the application frequently filters by:

• created_at
• order_date
• due_date
• updated_at
• event_time

Consider composite indexes when date filters combine with another key

Example query:

WHERE tenant_id = 17
  AND created_at >= '2026-04-01'
  AND created_at < '2026-05-01'
ORDER BY created_at DESC

This often benefits from an index shaped around:

• tenant_id
• created_at

Be careful with huge date-grouping reports on large raw event tables

Sometimes it is better to:

• pre-aggregate
• use summary tables
• or build reporting layers

especially when the same grouped report runs constantly.

A practical cross-database mindset

Because SQL date syntax differs so much, the best learning strategy is usually:

Learn the core tasks first

• current date
• current timestamp
• date arithmetic
• extract year/month/day
• truncate to month/day
• calculate differences
• filter by range
• group by time period

Then learn the exact syntax for your database

For example:

• PostgreSQL-style patterns
• MySQL-style functions
• SQL Server-style functions
• SQLite-style equivalents

This is much better than trying to memorize every dialect at once.

A practical workflow for date queries

When writing a date-based query, this sequence helps a lot:

Step 1

Decide whether you are working with:

• date only
• timestamp
• or timezone-aware timestamp logic

Step 2

Decide whether the task is:

• filtering
• grouping
• formatting
• date arithmetic
• difference calculation
• or categorization

Step 3

Prefer raw range filters where possible for filtering.

Step 4

Use extraction or truncation for grouping and display logic.

Step 5

Be explicit about period boundaries.

Step 6

Check whether timezone behavior matters.

This workflow prevents many common bugs.

FAQ

What are SQL date functions used for?

SQL date functions are used to filter rows by time, calculate date differences, extract year or month values, group data into periods like days or months, and build time-based reports or dashboards.

Are SQL date functions the same in every database?

No. The core ideas are similar, but function names and syntax differ across PostgreSQL, MySQL, SQL Server, SQLite, and other engines. You usually need to adapt examples to your specific database.

What is the safest way to filter SQL rows by a month or date range?

The safest and usually most performance-friendly pattern is to use an inclusive lower bound and an exclusive upper bound, such as created_at >= '2026-04-01' AND created_at < '2026-05-01'.

Why do SQL date queries often cause performance problems?

Date queries often become slow when developers wrap indexed date columns in functions inside WHERE clauses, use broad scans, or group huge datasets without indexes or pre-aggregation.

Final thoughts

SQL date functions are one of the most practical parts of SQL because time-based logic appears in almost every real system.

The most important ideas to remember are:

• dates and timestamps are not the same thing
• current date and current timestamp serve different purposes
• range filters are often safer than equality filters on timestamps
• inclusive lower bounds and exclusive upper bounds are usually the best pattern
• extraction and truncation help with reporting
• date differences help measure business processes
• and engine-specific syntax matters, even when the core logic is universal

If you understand those patterns clearly, you can solve a very large percentage of real SQL date problems with confidence.

And that is what makes date functions so valuable: they turn raw time data into useful filters, useful reports, and useful business answers.