Skip to main content

SQL Built-In Date and Time Functions

Dates and times are among the most frequently queried data types in any database. When was this order placed? How many days until the subscription expires? What were last month's sales? How old is this customer? Every reporting dashboard, billing system, scheduling feature, and analytics query depends on the ability to work with temporal data accurately.

SQL date functions let you retrieve the current date and time, extract individual parts like year or month, add and subtract time intervals, calculate the difference between two dates, and format dates for display. They are essential tools, but they are also one of the areas where syntax varies most across database systems.

This guide covers every essential date and time function with practical examples and outputs, shows you the syntax for each major database, and helps you avoid the common traps that make date logic one of the trickiest parts of SQL.

The Sample Data​

All examples use these tables:

CREATE TABLE employees (
    employee_id   INT PRIMARY KEY,
    first_name    VARCHAR(50),
    last_name     VARCHAR(50),
    hire_date     DATE,
    birth_date    DATE
);

CREATE TABLE orders (
    order_id      INT PRIMARY KEY,
    customer_id   INT,
    order_date    TIMESTAMP,
    ship_date     DATE,
    status        VARCHAR(20),
    total_amount  DECIMAL(10,2)
);

CREATE TABLE subscriptions (
    sub_id        INT PRIMARY KEY,
    customer_id   INT,
    plan_name     VARCHAR(50),
    start_date    DATE,
    end_date      DATE
);

INSERT INTO employees VALUES
(101, 'Alice',  'Martin',  '2019-03-15', '1990-07-22'),
(102, 'Bob',    'Jones',   '2021-07-01', '1985-11-03'),
(103, 'Carol',  'Smith',   '2022-01-10', '1992-04-18'),
(104, 'Dave',   'Wilson',  '2018-09-20', '1988-01-30'),
(105, 'Eve',    'Turner',  ' 2026-02-14', '1995-12-05');

INSERT INTO orders VALUES
(1001, 1, ' 2026-01-15 09:30:00', ' 2026-01-18', 'delivered',  155.97),
(1002, 2, ' 2026-03-22 14:15:00', ' 2026-03-25', 'delivered',  299.99),
(1003, 1, ' 2026-06-05 11:00:00', ' 2026-06-09', 'shipped',     45.48),
(1004, 3, ' 2026-06-07 16:45:00', NULL,         'processing',  89.99),
(1005, 2, ' 2026-06-10 08:20:00', NULL,         'pending',      9.99),
(1006, 1, ' 2026-06-14 19:00:00', NULL,         'pending',    310.00);

INSERT INTO subscriptions VALUES
(1, 1, 'Premium',  ' 2026-01-01', ' 2026-12-31'),
(2, 2, 'Basic',    ' 2026-03-15', ' 2026-09-14'),
(3, 3, 'Premium',  '2023-06-01', ' 2026-05-31'),
(4, 4, 'Basic',    ' 2026-06-01', '2025-05-31');

Getting the Current Date and Time​

The most basic date operation is retrieving the current date or timestamp. Every database supports this, but the syntax varies.

Syntax by Database​

FunctionPostgreSQLMySQLSQL ServerOracleSQLite
Current dateCURRENT_DATECURRENT_DATE or CURDATE()CAST(GETDATE() AS DATE)SYSDATE or CURRENT_DATEDATE('now')
Current timestampCURRENT_TIMESTAMP or NOW()CURRENT_TIMESTAMP or NOW()GETDATE() or CURRENT_TIMESTAMPSYSTIMESTAMP or CURRENT_TIMESTAMPDATETIME('now')
Current timeCURRENT_TIMECURRENT_TIME or CURTIME()CAST(GETDATE() AS TIME)Not directly availableTIME('now')

Examples​

-- PostgreSQL / MySQL
SELECT
    CURRENT_DATE      AS today,
    CURRENT_TIMESTAMP AS right_now,
    NOW()             AS also_now;
todayright_nowalso_now
2026-02-252026-02-25 13:34:032026-02-25 13:34:03

CURRENT_TIMESTAMP vs NOW()​

In most databases, CURRENT_TIMESTAMP and NOW() return the same value, but there is a subtle difference:

  • CURRENT_TIMESTAMP is SQL standard. It returns the timestamp at the start of the current transaction (or statement). It stays the same throughout the transaction.
  • NOW() behaves identically in PostgreSQL and MySQL: it returns the transaction start time.
  • CLOCK_TIMESTAMP() (PostgreSQL only) returns the actual wall-clock time, which changes during a long-running transaction.
-- PostgreSQL: within the same transaction, these are identical
BEGIN;
SELECT NOW();                -- 2026-02-25 13:34:22
-- ... do some work for 5 seconds ...
SELECT NOW();                -- 2026-02-25 13:34:22 (same!)
SELECT CLOCK_TIMESTAMP();    -- 2026-02-25 13:34:26 (actual current time)
COMMIT;
tip

For most application code, CURRENT_TIMESTAMP or NOW() is what you want. The transaction-level consistency ensures that all rows processed in the same transaction get the same timestamp, which is important for audit trails and consistency.

Use CLOCK_TIMESTAMP() (PostgreSQL) or SYSUTCDATETIME() (SQL Server) only when you need the actual wall-clock time during a long-running operation.

Practical: Default Column Values​

The most common use of current date/time functions is as column defaults:

CREATE TABLE audit_log (
    log_id      INT PRIMARY KEY,
    action      VARCHAR(50),
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    log_date    DATE DEFAULT CURRENT_DATE
);

INSERT INTO audit_log (log_id, action) VALUES (1, 'user_login');
-- created_at and log_date automatically filled with current date/time

EXTRACT and DATE_PART: Pulling Parts from Dates​

EXTRACT retrieves a specific component (year, month, day, hour, etc.) from a date or timestamp value.

Syntax​

-- Standard SQL / PostgreSQL / MySQL / Oracle
EXTRACT(part FROM date_value)

-- PostgreSQL also supports
DATE_PART('part', date_value)

-- SQL Server
DATEPART(part, date_value)
YEAR(date_value), MONTH(date_value), DAY(date_value)

-- SQLite
STRFTIME('%Y', date_value)  -- year
STRFTIME('%m', date_value)  -- month
STRFTIME('%d', date_value)  -- day

Available Parts​

PartReturnsExample (for 2026-06-15 14:30:45)
YEARYear2026
MONTHMonth number6
DAYDay of month15
HOURHour (24h)14
MINUTEMinute30
SECONDSecond45
DOW / WEEKDAYDay of weekVaries by database
DOYDay of year167
QUARTERQuarter (1-4)2
WEEKISO week number24
EPOCHSeconds since 1970-01-01 (PostgreSQL)1718454645

Examples​

-- PostgreSQL / MySQL / Oracle
SELECT
    order_id,
    order_date,
    EXTRACT(YEAR FROM order_date)    AS order_year,
    EXTRACT(MONTH FROM order_date)   AS order_month,
    EXTRACT(DAY FROM order_date)     AS order_day,
    EXTRACT(HOUR FROM order_date)    AS order_hour,
    EXTRACT(QUARTER FROM order_date) AS order_quarter
FROM orders;
order_idorder_dateorder_yearorder_monthorder_dayorder_hourorder_quarter
10012026-01-15 09:30:00202611591
10022026-03-22 14:15:002026322141
10032026-06-05 11:00:00202665112
10042026-06-07 16:45:00202667162
10052026-06-10 08:20:00202661082
10062026-06-14 19:00:002026614192

SQL Server equivalent:

SELECT
    order_id,
    YEAR(order_date)          AS order_year,
    MONTH(order_date)         AS order_month,
    DAY(order_date)           AS order_day,
    DATEPART(HOUR, order_date) AS order_hour,
    DATEPART(QUARTER, order_date) AS order_quarter
FROM orders;

Practical: Monthly Sales Report​

SELECT
    EXTRACT(YEAR FROM order_date) AS yr,
    EXTRACT(MONTH FROM order_date) AS mo,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_sales
FROM orders
GROUP BY
    EXTRACT(YEAR FROM order_date),
    EXTRACT(MONTH FROM order_date)
ORDER BY yr, mo;
yrmoorder_counttotal_sales
202611155.97
202631299.99
202664455.46

Practical: Day-of-Week Analysis​

-- PostgreSQL: DOW returns 0=Sunday through 6=Saturday
SELECT
    order_id,
    order_date,
    EXTRACT(DOW FROM order_date) AS day_num,
    CASE EXTRACT(DOW FROM order_date)
        WHEN 0 THEN 'Sunday'
        WHEN 1 THEN 'Monday'
        WHEN 2 THEN 'Tuesday'
        WHEN 3 THEN 'Wednesday'
        WHEN 4 THEN 'Thursday'
        WHEN 5 THEN 'Friday'
        WHEN 6 THEN 'Saturday'
    END AS day_name
FROM orders;
order_idorder_dateday_numday_name
10012026-01-15 09:30:001Monday
10022026-03-22 14:15:005Friday
10032026-06-05 11:00:003Wednesday
10042026-06-07 16:45:005Friday
10052026-06-10 08:20:001Monday
10062026-06-14 19:00:005Friday
warning

Day-of-week numbering varies by database:

DatabaseFunctionSundayMondaySaturday
PostgreSQL (DOW)EXTRACT(DOW FROM ...)016
PostgreSQL (ISODOW)EXTRACT(ISODOW FROM ...)716
MySQLDAYOFWEEK(date)127
MySQLWEEKDAY(date)605
SQL ServerDATEPART(WEEKDAY, ...)127 (default)

Always test your specific database's behavior before relying on day-of-week numbers in production queries.

Adding and Subtracting Time: INTERVAL Arithmetic​

One of the most common date operations is moving forward or backward in time: "30 days from now," "3 months ago," "next year."

PostgreSQL: INTERVAL Syntax​

PostgreSQL uses the most readable syntax with the INTERVAL keyword:

-- Add time
SELECT
    CURRENT_DATE + INTERVAL '30 days'   AS thirty_days_later,
    CURRENT_DATE + INTERVAL '3 months'  AS three_months_later,
    CURRENT_DATE + INTERVAL '1 year'    AS next_year,
    CURRENT_DATE - INTERVAL '7 days'    AS one_week_ago;
thirty_days_laterthree_months_laternext_yearone_week_ago
2026-07-152026-09-152025-06-152026-06-08
-- Combine intervals
SELECT
    CURRENT_TIMESTAMP + INTERVAL '2 hours 30 minutes' AS later_today,
    CURRENT_DATE - INTERVAL '1 year 6 months' AS eighteen_months_ago;
later_todayeighteen_months_ago
2026-06-15 17:00:45.1234562022-12-15

MySQL: DATE_ADD and DATE_SUB​

MySQL supports both the INTERVAL syntax and dedicated functions:

-- Function syntax
SELECT
    DATE_ADD(CURRENT_DATE, INTERVAL 30 DAY) AS thirty_days_later,
    DATE_SUB(CURRENT_DATE, INTERVAL 3 MONTH) AS three_months_ago,
    DATE_ADD(NOW(), INTERVAL 2 HOUR) AS two_hours_later;

-- Operator syntax (also works)
SELECT
    CURRENT_DATE + INTERVAL 30 DAY AS thirty_days_later,
    CURRENT_DATE - INTERVAL 7 DAY AS one_week_ago;
info

MySQL uses singular keywords without quotes for intervals: INTERVAL 30 DAY, INTERVAL 3 MONTH, INTERVAL 1 YEAR. PostgreSQL uses quoted strings: INTERVAL '30 days', INTERVAL '3 months'.

SQL Server: DATEADD​

SQL Server uses the DATEADD function exclusively:

SELECT
    DATEADD(DAY, 30, GETDATE())    AS thirty_days_later,
    DATEADD(MONTH, -3, GETDATE())  AS three_months_ago,
    DATEADD(YEAR, 1, GETDATE())    AS next_year,
    DATEADD(HOUR, 2, GETDATE())    AS two_hours_later;

Negative values subtract time. The syntax is DATEADD(part, number, date).

Oracle: Simple Arithmetic and ADD_MONTHS​

-- Oracle: add days with simple arithmetic
SELECT SYSDATE + 30 AS thirty_days_later FROM DUAL;

-- Oracle: add months
SELECT ADD_MONTHS(SYSDATE, 3) AS three_months_later FROM DUAL;

-- Oracle: INTERVAL syntax also works
SELECT SYSDATE + INTERVAL '30' DAY AS thirty_days_later FROM DUAL;

Cross-Database Summary​

OperationPostgreSQLMySQLSQL ServerOracle
Add 30 daysdate + INTERVAL '30 days'DATE_ADD(date, INTERVAL 30 DAY)DATEADD(DAY, 30, date)date + 30
Subtract 3 monthsdate - INTERVAL '3 months'DATE_SUB(date, INTERVAL 3 MONTH)DATEADD(MONTH, -3, date)ADD_MONTHS(date, -3)
Add 2 hoursts + INTERVAL '2 hours'DATE_ADD(ts, INTERVAL 2 HOUR)DATEADD(HOUR, 2, ts)ts + INTERVAL '2' HOUR

Practical: Find Expiring Subscriptions​

-- PostgreSQL: subscriptions expiring within the next 30 days
SELECT
    sub_id,
    customer_id,
    plan_name,
    end_date,
    end_date - CURRENT_DATE AS days_remaining
FROM subscriptions
WHERE end_date BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '30 days';

Practical: Orders from the Last 7 Days​

-- PostgreSQL
SELECT * FROM orders
WHERE order_date >= CURRENT_TIMESTAMP - INTERVAL '7 days';

-- MySQL
SELECT * FROM orders
WHERE order_date >= NOW() - INTERVAL 7 DAY;

-- SQL Server
SELECT * FROM orders
WHERE order_date >= DATEADD(DAY, -7, GETDATE());

Practical: Set a Reminder Date​

-- Schedule follow-up 14 days after order
SELECT
    order_id,
    order_date,
    order_date + INTERVAL '14 days' AS follow_up_date
FROM orders
WHERE status = 'pending';
order_idorder_datefollow_up_date
10052026-06-10 08:20:002026-06-24 08:20:00
10062026-06-14 19:00:002026-06-28 19:00:00

Calculating Date Differences: DATEDIFF and AGE​

Calculating the time between two dates is fundamental for reporting, SLA tracking, age calculations, and billing logic.

PostgreSQL: Subtraction and AGE()​

PostgreSQL makes date arithmetic simple with direct subtraction and the AGE() function:

-- Direct subtraction returns an integer (days)
SELECT
    order_id,
    order_date::DATE AS ordered,
    ship_date,
    ship_date - order_date::DATE AS days_to_ship
FROM orders
WHERE ship_date IS NOT NULL;
order_idorderedship_datedays_to_ship
10012026-01-152026-01-183
10022026-03-222026-03-253
10032026-06-052026-06-094

AGE() returns a human-readable interval:

SELECT
    first_name,
    birth_date,
    AGE(CURRENT_DATE, birth_date) AS age,
    EXTRACT(YEAR FROM AGE(CURRENT_DATE, birth_date)) AS age_years
FROM employees;
first_namebirth_dateageage_years
Alice1990-07-2233 years 10 mons 24 days33
Bob1985-11-0338 years 7 mons 12 days38
Carol1992-04-1832 years 1 mon 28 days32
Dave1988-01-3036 years 4 mons 16 days36
Eve1995-12-0528 years 6 mons 10 days28

AGE() with a single argument calculates the difference from CURRENT_DATE:

-- Equivalent: AGE(birth_date) is the same as AGE(CURRENT_DATE, birth_date)
SELECT first_name, AGE(birth_date) AS age FROM employees;

MySQL: DATEDIFF and TIMESTAMPDIFF​

-- DATEDIFF returns the difference in days
SELECT
    order_id,
    order_date,
    ship_date,
    DATEDIFF(ship_date, order_date) AS days_to_ship
FROM orders
WHERE ship_date IS NOT NULL;
-- TIMESTAMPDIFF for other units
SELECT
    first_name,
    birth_date,
    TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age_years,
    TIMESTAMPDIFF(MONTH, birth_date, CURDATE()) AS age_months
FROM employees;
warning

MySQL's DATEDIFF argument order is DATEDIFF(end_date, start_date), which is the opposite of SQL Server's. This is a common source of bugs when migrating between databases.

-- MySQL: end_date first
SELECT DATEDIFF(' 2026-06-15', ' 2026-06-01');       -- Result: 14

-- SQL Server: start_date in the middle
SELECT DATEDIFF(DAY, ' 2026-06-01', ' 2026-06-15');  -- Result: 14

SQL Server: DATEDIFF​

SQL Server's DATEDIFF requires specifying the unit:

SELECT
    order_id,
    DATEDIFF(DAY, order_date, ship_date) AS days_to_ship,
    DATEDIFF(HOUR, order_date, ship_date) AS hours_to_ship
FROM orders
WHERE ship_date IS NOT NULL;
-- Age in years
SELECT
    first_name,
    DATEDIFF(YEAR, birth_date, GETDATE()) AS age_approx,
    DATEDIFF(MONTH, birth_date, GETDATE()) AS age_months
FROM employees;
info

SQL Server's DATEDIFF(YEAR, ...) counts calendar year boundaries crossed, not full years. Someone born on 1990-12-31 would show as 34 years old on 2026-01-01, even though they are only one day past their 33rd birthday. For accurate age calculation in SQL Server:

SELECT
    first_name,
    DATEDIFF(YEAR, birth_date, GETDATE())
    - CASE
        WHEN DATEADD(YEAR, DATEDIFF(YEAR, birth_date, GETDATE()), birth_date) > GETDATE()
        THEN 1 ELSE 0
      END AS accurate_age
FROM employees;

Oracle: Subtraction and MONTHS_BETWEEN​

-- Subtraction returns days as a decimal
SELECT
    order_id,
    ship_date - CAST(order_date AS DATE) AS days_to_ship
FROM orders
WHERE ship_date IS NOT NULL;
-- MONTHS_BETWEEN for month differences
SELECT
    first_name,
    FLOOR(MONTHS_BETWEEN(SYSDATE, birth_date) / 12) AS age_years
FROM employees;

Cross-Database Date Difference Summary​

OperationPostgreSQLMySQLSQL ServerOracle
Days betweendate2 - date1DATEDIFF(date2, date1)DATEDIFF(DAY, date1, date2)date2 - date1
Months betweenEXTRACT(YEAR FROM AGE(...)) * 12 + EXTRACT(MONTH FROM AGE(...))TIMESTAMPDIFF(MONTH, d1, d2)DATEDIFF(MONTH, d1, d2)MONTHS_BETWEEN(d2, d1)
Years (age)EXTRACT(YEAR FROM AGE(d))TIMESTAMPDIFF(YEAR, d1, d2)See accurate formula aboveFLOOR(MONTHS_BETWEEN / 12)
Full intervalAGE(d2, d1)Not availableNot availableNot available

Practical: Employee Tenure​

-- PostgreSQL
SELECT
    first_name,
    last_name,
    hire_date,
    EXTRACT(YEAR FROM AGE(CURRENT_DATE, hire_date)) AS years_employed,
    CURRENT_DATE - hire_date AS total_days
FROM employees
ORDER BY hire_date;
first_namelast_namehire_dateyears_employedtotal_days
DaveWilson2018-09-2052095
AliceMartin2019-03-1551918
BobJones2021-07-0121080
CarolSmith2022-01-102887
EveTurner2026-02-140122

Practical: Shipping Time Analysis​

-- PostgreSQL
SELECT
    status,
    COUNT(*) AS order_count,
    ROUND(AVG(ship_date - order_date::DATE), 1) AS avg_days_to_ship,
    MAX(ship_date - order_date::DATE) AS max_days_to_ship
FROM orders
WHERE ship_date IS NOT NULL
GROUP BY status;
statusorder_countavg_days_to_shipmax_days_to_ship
delivered23.03
shipped14.04

Formatting Dates​

Raw date values like 2026-06-15 are fine for databases but often need formatting for user-facing output.

PostgreSQL: TO_CHAR​

SELECT
    order_id,
    order_date,
    TO_CHAR(order_date, 'YYYY-MM-DD') AS iso_format,
    TO_CHAR(order_date, 'Mon DD, YYYY') AS readable,
    TO_CHAR(order_date, 'DD/MM/YYYY') AS european,
    TO_CHAR(order_date, 'Day, Month DD, YYYY') AS full_format,
    TO_CHAR(order_date, 'HH12:MI AM') AS time_12h
FROM orders
WHERE order_id = 1001;
order_idiso_formatreadableeuropeanfull_formattime_12h
10012026-01-15Jan 15, 202615/01/ 2026Monday , January 15, 202609:30 AM

Common PostgreSQL format codes:

CodeMeaningExample
YYYY4-digit year2026
YY2-digit year24
MMMonth number (01-12)06
MonAbbreviated monthJun
MonthFull month nameJune
DDDay of month (01-31)15
DayFull day nameSaturday
DyAbbreviated daySat
HH24Hour 00-2314
HH12Hour 01-1202
MIMinute30
SSSecond45
AM / PMAM/PM indicatorPM
QQuarter2

MySQL: DATE_FORMAT​

SELECT
    order_id,
    DATE_FORMAT(order_date, '%Y-%m-%d') AS iso_format,
    DATE_FORMAT(order_date, '%b %d, %Y') AS readable,
    DATE_FORMAT(order_date, '%d/%m/%Y') AS european,
    DATE_FORMAT(order_date, '%W, %M %d, %Y') AS full_format,
    DATE_FORMAT(order_date, '%h:%i %p') AS time_12h
FROM orders
WHERE order_id = 1001;

Common MySQL format codes:

CodeMeaningExample
%Y4-digit year2026
%mMonth number (01-12)06
%bAbbreviated monthJun
%MFull month nameJune
%dDay of month (01-31)15
%WFull day nameSaturday
%HHour 00-2314
%hHour 01-1202
%iMinute30
%sSecond45
%pAM/PMPM

SQL Server: FORMAT and CONVERT​

-- FORMAT (SQL Server 2012+): uses .NET format strings
SELECT
    FORMAT(order_date, 'yyyy-MM-dd') AS iso_format,
    FORMAT(order_date, 'MMM dd, yyyy') AS readable,
    FORMAT(order_date, 'dd/MM/yyyy') AS european,
    FORMAT(order_date, 'hh:mm tt') AS time_12h
FROM orders WHERE order_id = 1001;

-- CONVERT with style codes (older approach, faster)
SELECT
    CONVERT(VARCHAR, order_date, 23) AS iso_format,     -- yyyy-mm-dd
    CONVERT(VARCHAR, order_date, 103) AS european,       -- dd/mm/yyyy
    CONVERT(VARCHAR, order_date, 100) AS us_format       -- Mon dd yyyy hh:mmAM
FROM orders WHERE order_id = 1001;

SQLite: STRFTIME​

SELECT
    order_id,
    STRFTIME('%Y-%m-%d', order_date) AS iso_format,
    STRFTIME('%d/%m/%Y', order_date) AS european,
    STRFTIME('%H:%M', order_date) AS time_24h
FROM orders WHERE order_id = 1001;
tip

Date formatting belongs in the presentation layer, not the database layer. Format dates in your application code (JavaScript, Python, Java, etc.) rather than in SQL whenever possible. Application-level formatting supports localization, user preferences, and timezone conversion much better than SQL.

Use SQL date formatting primarily for:

  • Database reports and exports
  • Quick ad-hoc queries
  • Generated SQL output (like CSV exports)

Truncating Dates: DATE_TRUNC​

DATE_TRUNC rounds a timestamp down to a specified precision. It is invaluable for grouping by time periods.

Syntax​

-- PostgreSQL
DATE_TRUNC('month', timestamp)

-- MySQL (8.0+ or use workarounds)
DATE_FORMAT(timestamp, '%Y-%m-01')  -- truncate to month
DATE(timestamp)                     -- truncate to day

-- SQL Server
DATETRUNC(MONTH, timestamp)         -- SQL Server 2022+
DATEADD(MONTH, DATEDIFF(MONTH, 0, timestamp), 0)  -- older versions

PostgreSQL Examples​

SELECT
    order_date,
    DATE_TRUNC('year', order_date)    AS year_start,
    DATE_TRUNC('month', order_date)   AS month_start,
    DATE_TRUNC('week', order_date)    AS week_start,
    DATE_TRUNC('day', order_date)     AS day_start,
    DATE_TRUNC('hour', order_date)    AS hour_start
FROM orders
WHERE order_id = 1003;
order_dateyear_startmonth_startweek_startday_starthour_start
2026-06-05 11:00:002026-01-01 00:00:002026-06-01 00:00:002026-06-03 00:00:002026-06-05 00:00:002026-06-05 11:00:00

Monthly sales using DATE_TRUNC:

SELECT
    DATE_TRUNC('month', order_date)::DATE AS month,
    COUNT(*) AS orders,
    SUM(total_amount) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;
monthordersrevenue
2026-01-011155.97
2026-03-011299.99
2026-06-014455.46

This is cleaner than using EXTRACT(YEAR ...) || '-' || EXTRACT(MONTH ...) and groups correctly even when data spans multiple years.

Combining Date Functions: Practical Recipes​

Find Records from "This Month"​

-- PostgreSQL
SELECT * FROM orders
WHERE DATE_TRUNC('month', order_date) = DATE_TRUNC('month', CURRENT_TIMESTAMP);

-- MySQL
SELECT * FROM orders
WHERE YEAR(order_date) = YEAR(CURDATE())
  AND MONTH(order_date) = MONTH(CURDATE());

-- SQL Server
SELECT * FROM orders
WHERE YEAR(order_date) = YEAR(GETDATE())
  AND MONTH(order_date) = MONTH(GETDATE());

Calculate Business Days Until Deadline​

-- PostgreSQL: rough estimate excluding weekends
SELECT
    sub_id,
    plan_name,
    end_date,
    end_date - CURRENT_DATE AS calendar_days,
    (end_date - CURRENT_DATE)
        - 2 * ((end_date - CURRENT_DATE) / 7)  -- subtract weekends
        AS approx_business_days
FROM subscriptions
WHERE end_date > CURRENT_DATE;

Year-Over-Year Comparison​

-- PostgreSQL: compare current month to same month last year
SELECT
    DATE_TRUNC('month', order_date)::DATE AS month,
    SUM(total_amount) AS revenue,
    LAG(SUM(total_amount)) OVER (ORDER BY DATE_TRUNC('month', order_date)) AS prev_month_revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

Generate a Date Range for Reporting​

-- PostgreSQL: generate a series of dates
SELECT generate_series(
    ' 2026-01-01'::DATE,
    ' 2026-06-30'::DATE,
    '1 month'::INTERVAL
)::DATE AS month_start;
month_start
2026-01-01
2026-02-01
2026-03-01
2026-04-01
2026-05-01
2026-06-01

This is useful for ensuring every month appears in a report even if no orders occurred:

SELECT
    ms.month_start,
    COALESCE(COUNT(o.order_id), 0) AS order_count,
    COALESCE(SUM(o.total_amount), 0) AS revenue
FROM generate_series(' 2026-01-01'::DATE, ' 2026-06-01'::DATE, '1 month') AS ms(month_start)
LEFT JOIN orders o ON DATE_TRUNC('month', o.order_date)::DATE = ms.month_start
GROUP BY ms.month_start
ORDER BY ms.month_start;
month_startorder_countrevenue
2026-01-011155.97
2026-02-0100
2026-03-011299.99
2026-04-0100
2026-05-0100
2026-06-014455.46

Common Mistakes​

Mistake 1: Comparing Timestamps to Dates Without Thinking​

-- This misses orders on June 15th that have a time component
SELECT * FROM orders WHERE order_date = ' 2026-06-14';
-- Only matches if order_date is exactly ' 2026-06-14 00:00:00.000000'

Fix: Use a range or cast to date:

-- Option A: range comparison
SELECT * FROM orders
WHERE order_date >= ' 2026-06-14'
  AND order_date < ' 2026-06-15';

-- Option B: cast timestamp to date
SELECT * FROM orders
WHERE order_date::DATE = ' 2026-06-14';  -- PostgreSQL
WHERE DATE(order_date) = ' 2026-06-14';  -- MySQL
WHERE CAST(order_date AS DATE) = ' 2026-06-14';  -- SQL Server
warning

Option B (casting in WHERE) prevents the database from using an index on order_date because a function is applied to the column. Option A (range comparison) is always more efficient. Use ranges for filtered queries and casting only for display.

Mistake 2: Using Functions on Indexed Date Columns​

-- Slow: function prevents index usage
SELECT * FROM orders
WHERE EXTRACT(YEAR FROM order_date) =  2026
  AND EXTRACT(MONTH FROM order_date) = 6;

-- Fast: range comparison uses the index
SELECT * FROM orders
WHERE order_date >= ' 2026-06-01'
  AND order_date < ' 2026-07-01';

Mistake 3: Ignoring Time Zones​

-- This might return different results depending on server timezone
SELECT * FROM orders WHERE order_date::DATE = CURRENT_DATE;

If your server is in UTC but your users are in EST, CURRENT_DATE at 10 PM EST is already "tomorrow" in UTC. Use timezone-aware types and conversions:

-- PostgreSQL: convert to user's timezone before comparing
SELECT * FROM orders
WHERE order_date AT TIME ZONE 'America/New_York' >= CURRENT_DATE;

Mistake 4: Month Arithmetic Edge Cases​

Adding one month to January 31 produces different results across databases because February 31 does not exist:

-- What is January 31 + 1 month?
-- PostgreSQL:  2026-02-29 (clips to end of February in leap year)
SELECT DATE ' 2026-01-31' + INTERVAL '1 month';

-- MySQL:  2026-02-29
SELECT DATE_ADD(' 2026-01-31', INTERVAL 1 MONTH);

-- SQL Server:  2026-02-29
SELECT DATEADD(MONTH, 1, ' 2026-01-31');

For non-leap years, January 31 + 1 month returns February 28 in most databases. This "end of month" clamping behavior can cause subtle bugs in billing and subscription logic.

tip

If your application requires exact 30-day or 365-day intervals (common in finance and legal contexts), use INTERVAL '30 days' instead of INTERVAL '1 month', and INTERVAL '365 days' instead of INTERVAL '1 year'. This avoids the month-length ambiguity entirely.

Mistake 5: BETWEEN with Timestamps​

-- BETWEEN is inclusive on both ends. This includes midnight of June 30.
SELECT * FROM orders
WHERE order_date BETWEEN ' 2026-06-01' AND ' 2026-06-30';
-- Misses orders on June 30 that have a time component after 00:00:00!
-- ' 2026-06-30 15:00:00' > ' 2026-06-30 00:00:00' so it's excluded.

Fix:

SELECT * FROM orders
WHERE order_date >= ' 2026-06-01'
  AND order_date < ' 2026-07-01';
-- Catches every timestamp in June, regardless of time component

Quick Reference​

TaskPostgreSQLMySQLSQL Server
Current dateCURRENT_DATECURDATE()CAST(GETDATE() AS DATE)
Current timestampNOW()NOW()GETDATE()
Extract yearEXTRACT(YEAR FROM d)YEAR(d)YEAR(d)
Extract monthEXTRACT(MONTH FROM d)MONTH(d)MONTH(d)
Add daysd + INTERVAL '30 days'DATE_ADD(d, INTERVAL 30 DAY)DATEADD(DAY, 30, d)
Subtract monthsd - INTERVAL '3 months'DATE_SUB(d, INTERVAL 3 MONTH)DATEADD(MONTH, -3, d)
Days betweend2 - d1DATEDIFF(d2, d1)DATEDIFF(DAY, d1, d2)
Age in yearsEXTRACT(YEAR FROM AGE(d))TIMESTAMPDIFF(YEAR, d, NOW())Complex formula
Truncate to monthDATE_TRUNC('month', d)DATE_FORMAT(d, '%Y-%m-01')DATETRUNC(MONTH, d) (2022+)
Format dateTO_CHAR(d, 'Mon DD, YYYY')DATE_FORMAT(d, '%b %d, %Y')FORMAT(d, 'MMM dd, yyyy')

Summary​

SQL date functions give you the tools to work with temporal data directly in your queries. From retrieving the current time to calculating ages, from grouping by month to checking subscription expirations, dates are central to almost every database application.

Key takeaways:

  • CURRENT_DATE and CURRENT_TIMESTAMP / NOW() return the current date and time. They return the transaction start time, not the wall-clock time.
  • EXTRACT pulls individual components (year, month, day, hour) from a date. SQL Server uses DATEPART() or YEAR()/MONTH()/DAY() instead.
  • Interval arithmetic adds or subtracts time from dates. PostgreSQL uses INTERVAL '30 days', MySQL uses INTERVAL 30 DAY or DATE_ADD, SQL Server uses DATEADD(), and Oracle uses simple addition for days.
  • Date differences are calculated with subtraction (PostgreSQL, Oracle), DATEDIFF (MySQL, SQL Server), or AGE() (PostgreSQL). Watch the argument order, which differs between MySQL and SQL Server.
  • DATE_TRUNC is essential for time-period grouping. It rounds timestamps down to the start of the specified period (year, month, week, day, hour).
  • Date formatting uses TO_CHAR (PostgreSQL/Oracle), DATE_FORMAT (MySQL), or FORMAT (SQL Server). Keep formatting in the application layer when possible.
  • Use range comparisons (>= start AND < end) instead of functions on indexed date columns or BETWEEN with timestamps.
  • Beware of time zones, month-length edge cases, and the difference between date and timestamp comparisons. These subtle issues cause the majority of date-related bugs.
  • Date function syntax varies more across databases than almost any other SQL feature. Always test your date logic against your specific database version.

Table of Contents