SQL Date Functions Guide: Practical Examples (2026)

Mastering sql date functions is essential for tracking time-series metrics, calculating month-over-month growth, and building accurate retention cohorts without silent timestamp bugs. Whether aggregating revenue by month using DATE_TRUNC or isolating seasonal cycles with EXTRACT, applying correct date logic ensures analytical integrity.

This sql date functions practical guide provides production-tested sql date functions examples to teach you how to use sql date functions effectively. We examine real-world queries against our verified 2,000-order e-commerce database, demonstrate interval math, avoid the silent BETWEEN date trap, and build reproducible cohort queries. For complementary SQL techniques, explore our SQL CTE Guide, SQL Window Functions Guide, and execution logic in WHERE vs HAVING in SQL.

Quick Dialect Syntax Matrix

1. EXTRACT: Isolating Date Components for Seasonality

The EXTRACT() function pulls out a specific numeric piece (year, month, day of week, hour) from a date or timestamp.

Syntax

EXTRACT(field FROM source)

Orders Per Calendar Year

Let's group our 2,000 orders by year:

SELECT EXTRACT(YEAR FROM order_date)::int AS order_year,
       COUNT(*)                           AS total_orders
FROM orders
GROUP BY order_year
ORDER BY order_year;

The Output

Notice that 2025 has fewer orders (263) because the dataset ends on June 1, 2025.

Analyzing Day-of-Week Seasonality

EXTRACT(DOW FROM order_date) returns the day of the week as an integer (0 for Sunday, 6 for Saturday):

SELECT EXTRACT(DOW FROM order_date)::int AS day_of_week,
       COUNT(*)                          AS order_volume
FROM orders
WHERE status = 'completed'
GROUP BY day_of_week
ORDER BY day_of_week;

EXTRACT is perfect for answering questions like: "Which day of the week has the highest purchase volume across all historical data?" However, because it discards the year, you cannot use EXTRACT alone to build a chronological timeline.

2. DATE_TRUNC: Building Continuous Trendlines

To track monthly or weekly trends over time, you must keep the year and month bound together. DATE_TRUNC() rounds a timestamp down to the beginning of the specified interval.

Truncation Examples

• DATE_TRUNC('month', '2024-03-24 15:30:00'::timestamp) → 2024-03-01 00:00:00
• DATE_TRUNC('year', '2024-03-24 15:30:00'::timestamp) → 2024-01-01 00:00:00
• DATE_TRUNC('day', '2024-03-24 15:30:00'::timestamp) → 2024-03-24 00:00:00

Monthly Revenue Timeline Query

SELECT DATE_TRUNC('month', order_date)::date AS order_month,
       COUNT(*)                               AS completed_orders,
       ROUND(SUM(total_amount), 2)            AS monthly_revenue
FROM orders
WHERE status = 'completed'
GROUP BY order_month
ORDER BY order_month;

The Output (First 4 Months)

Trap vs. Fix: Multi-Year Seasonality Collapse

A frequent analytical pitfall is grouping multi-year transactions by EXTRACT(MONTH FROM col) when attempting to report business trendlines.

⚠️ The Seasonality Trap Query

-- TRAP: Groups all Januarys (2022, 2023, 2024, 2025) into a single bucket
SELECT EXTRACT(MONTH FROM order_date)::int AS month_num,
       COUNT(*)                            AS order_count,
       ROUND(SUM(total_amount), 2)         AS gross_revenue
FROM orders
WHERE status = 'completed'
GROUP BY month_num
ORDER BY month_num;

Sandbox Output: 12 Rows (Multi-Year Data Merged)

This query collapses four distinct years into 12 rows, completely masking growth or churn between 2022 and 2025.

✅ The Continuous Timeline Fix Query

-- FIX: Preserves chronological order across 42 distinct calendar months
SELECT DATE_TRUNC('month', order_date)::date AS order_month,
       COUNT(*)                               AS order_count,
       ROUND(SUM(total_amount), 2)            AS gross_revenue
FROM orders
WHERE status = 'completed'
GROUP BY order_month
ORDER BY order_month;

Sandbox Output: 42 Distinct Monthly Cohorts

Grouping by DATE_TRUNC('month', order_date)::date correctly preserves each calendar month across multi-year history for accurate trend charts.

3. The BETWEEN Trap on Dates: Why Production Queries Drop Data

Filtering date ranges is where many analysts accidentally lose data. Look at this query designed to pull all orders in January 2024:

-- ⚠️ THE TEMPTING (BUT DANGEROUS) WAY
SELECT COUNT(*) AS total_orders
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';

Result in our dataset: 47 orders.

Now look at the safe half-open range:

-- ✅ THE BULLETPROOF WAY: Half-Open Range
SELECT COUNT(*) AS total_orders
FROM orders
WHERE order_date >= '2024-01-01'
  AND order_date <  '2024-02-01';

Result in our dataset: 47 orders.

Why They Match Here — And Why It Breaks in Production

In our synthetic educational dataset, every order_date is stored exactly at midnight (00:00:00). Therefore, the 31st at midnight matches both queries.

However, in production systems, timestamps contain hours, minutes, and seconds (e.g. 2024-01-31 16:45:12).

BETWEEN '2024-01-01' AND '2024-01-31'
is expanded by SQL to:
>= '2024-01-01 00:00:00' AND <= '2024-01-31 00:00:00'

Any order placed at 10:00 AM on January 31st is strictly greater than 2024-01-31 00:00:00. BETWEEN silently drops the entire last day of the month without any warning or error.

4. Date Math with INTERVAL

SQL allows dynamic arithmetic using the INTERVAL keyword, enabling flexible rolling windows without hardcoded dates.

Rolling 90-Day Window Filter

SELECT COUNT(*)                   AS orders_last_90d,
       ROUND(SUM(total_amount), 2) AS revenue_last_90d
FROM orders
WHERE order_date >= DATE '2025-06-01' - INTERVAL '90 days'
  AND order_date <  DATE '2025-06-01'
  AND status = 'completed';

In a production dashboard, replace the static '2025-06-01' with CURRENT_DATE:

WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
  AND order_date <  CURRENT_DATE

Common INTERVAL Expressions

• INTERVAL '7 days' / INTERVAL '1 week'
• INTERVAL '1 month' / INTERVAL '3 months'
• INTERVAL '1 year'
• INTERVAL '2 hours 30 minutes'

5. Month-over-Month (MoM) Growth Analysis

Combining DATE_TRUNC with the LAG() window function allows you to calculate month-over-month growth in a clean, reproducible query.

WITH monthly_metrics AS (
    SELECT DATE_TRUNC('month', order_date)::date AS order_month,
       ROUND(SUM(total_amount), 2)               AS revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY order_month
)
SELECT order_month,
       revenue,
       LAG(revenue, 1) OVER (ORDER BY order_month) AS prev_month_revenue,
       ROUND(revenue - LAG(revenue, 1) OVER (ORDER BY order_month), 2) AS mom_change,
       ROUND(
           ((revenue - LAG(revenue, 1) OVER (ORDER BY order_month)) / 
           NULLIF(LAG(revenue, 1) OVER (ORDER BY order_month), 0)) * 100, 
           1
       ) AS mom_growth_pct
FROM monthly_metrics
ORDER BY order_month;

The Output (First 3 Months)

6. Cohort Analysis Foundation: First-Order Month

In product analytics, cohort retention tracks groups of customers who signed up or made their first purchase in the same month.

Finding each customer's cohort acquisition month requires MIN(DATE_TRUNC('month', order_date)):

SELECT customer_id,
       MIN(DATE_TRUNC('month', order_date))::date AS first_order_month
FROM orders
WHERE customer_id IS NOT NULL
GROUP BY customer_id
ORDER BY first_order_month, customer_id
LIMIT 10;

In our dataset, the first-order cohorts start in January 2022 with 47 customers, followed by 43 customers in February 2022, forming the baseline for cohort retention curves.

7. Date Function Decision Guide

8. Defensive Coding Best Practices for SQL Dates

When writing production SQL date queries, adhere to these five defensive engineering rules:

1. Always Use Half-Open Ranges for Timestamps: Replace BETWEEN '2024-01-01' AND '2024-01-31' with >= '2024-01-01' AND < '2024-02-01'. This eliminates boundary cutoff bugs regardless of millisecond precision.
2. Never Group by EXTRACT for Trends: Use DATE_TRUNC('month', col)::date for continuous trendlines. Reserve EXTRACT(MONTH FROM col) exclusively for multi-year seasonality indexes.
3. Explicitly Cast Truncations with ::date: In PostgreSQL, DATE_TRUNC returns a timestamp with 00:00:00. Appending ::date strips the time component and ensures clean joins and BI dashboard formatting.
4. Standardize on UTC with Explicit Conversion: Store all event records in TIMESTAMPTZ (UTC). Convert timestamps to business timezones at report runtime using col AT TIME ZONE 'America/New_York'.
5. Enforce ISO 8601 String Literals: Always write string dates as 'YYYY-MM-DD' or 'YYYY-MM-DD HH24:MI:SS' to avoid locale-specific parsing errors (MM/DD/YYYY vs DD/MM/YYYY).

9. Summary & Hands-on Practice

• Use DATE_TRUNC() when preserving continuous timelines for charts, dashboards, and MoM calculations.
• Use EXTRACT() when isolating seasonal cycles across multiple years.
• Avoid BETWEEN for timestamp filtering—always use >= start AND < next_period to prevent losing the final day's transactions.
• Cast truncated timestamps with ::date for clean, standards-compliant date outputs.

Practice live date queries, MoM growth calculations, and cohort analyses in the Interactive SQL Practice Sandbox or build end-to-end data pipelines in our Data Analyst Career Track. To master query structure and readability, explore our SQL CTE Guide.