SQL Built-In Date and Time Functions

Master SQL date functions for current timestamps, extracting years/months, adding intervals, calculating age, and formatting dates with database-specific syntax and best practices.

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

Function	PostgreSQL	MySQL	SQL Server	Oracle	SQLite
Current date	`CURRENT_DATE`	`CURRENT_DATE` or `CURDATE()`	`CAST(GETDATE() AS DATE)`	`SYSDATE` or `CURRENT_DATE`	`DATE('now')`
Current timestamp	`CURRENT_TIMESTAMP` or `NOW()`	`CURRENT_TIMESTAMP` or `NOW()`	`GETDATE()` or `CURRENT_TIMESTAMP`	`SYSTIMESTAMP` or `CURRENT_TIMESTAMP`	`DATETIME('now')`
Current time	`CURRENT_TIME`	`CURRENT_TIME` or `CURTIME()`	`CAST(GETDATE() AS TIME)`	Not directly available	`TIME('now')`

Examples

-- PostgreSQL / MySQL
SELECT
    CURRENT_DATE      AS today,
    CURRENT_TIMESTAMP AS right_now,
    NOW()             AS also_now;

today	right_now	also_now
2026-02-25	2026-02-25 13:34:03	2026-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;

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

Part	Returns	Example (for 2026-06-15 14:30:45)
`YEAR`	Year	2026
`MONTH`	Month number	6
`DAY`	Day of month	15
`HOUR`	Hour (24h)	14
`MINUTE`	Minute	30
`SECOND`	Second	45
`DOW` / `WEEKDAY`	Day of week	Varies by database
`DOY`	Day of year	167
`QUARTER`	Quarter (1-4)	2
`WEEK`	ISO week number	24
`EPOCH`	Seconds 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_id	order_date	order_year	order_month	order_day	order_hour	order_quarter
1001	2026-01-15 09:30:00	2026	1	15	9	1
1002	2026-03-22 14:15:00	2026	3	22	14	1
1003	2026-06-05 11:00:00	2026	6	5	11	2
1004	2026-06-07 16:45:00	2026	6	7	16	2
1005	2026-06-10 08:20:00	2026	6	10	8	2
1006	2026-06-14 19:00:00	2026	6	14	19	2

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;

yr	mo	order_count	total_sales
2026	1	1	155.97
2026	3	1	299.99
2026	6	4	455.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_id	order_date	day_num	day_name
1001	2026-01-15 09:30:00	1	Monday
1002	2026-03-22 14:15:00	5	Friday
1003	2026-06-05 11:00:00	3	Wednesday
1004	2026-06-07 16:45:00	5	Friday
1005	2026-06-10 08:20:00	1	Monday
1006	2026-06-14 19:00:00	5	Friday

Warning

Day-of-week numbering varies by database:

Database	Function	Sunday	Monday	Saturday
PostgreSQL (`DOW`)	`EXTRACT(DOW FROM ...)`	0	1	6
PostgreSQL (`ISODOW`)	`EXTRACT(ISODOW FROM ...)`	7	1	6
MySQL	`DAYOFWEEK(date)`	1	2	7
MySQL	`WEEKDAY(date)`	6	0	5
SQL Server	`DATEPART(WEEKDAY, ...)`	1	2	7 (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_later	three_months_later	next_year	one_week_ago
2026-07-15	2026-09-15	2025-06-15	2026-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_today	eighteen_months_ago
2026-06-15 17:00:45.123456	2022-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;

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

Operation	PostgreSQL	MySQL	SQL Server	Oracle
Add 30 days	`date + INTERVAL '30 days'`	`DATE_ADD(date, INTERVAL 30 DAY)`	`DATEADD(DAY, 30, date)`	`date + 30`
Subtract 3 months	`date - INTERVAL '3 months'`	`DATE_SUB(date, INTERVAL 3 MONTH)`	`DATEADD(MONTH, -3, date)`	`ADD_MONTHS(date, -3)`
Add 2 hours	`ts + 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_id	order_date	follow_up_date
1005	2026-06-10 08:20:00	2026-06-24 08:20:00
1006	2026-06-14 19:00:00	2026-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_id	ordered	ship_date	days_to_ship
1001	2026-01-15	2026-01-18	3
1002	2026-03-22	2026-03-25	3
1003	2026-06-05	2026-06-09	4

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_name	birth_date	age	age_years
Alice	1990-07-22	33 years 10 mons 24 days	33
Bob	1985-11-03	38 years 7 mons 12 days	38
Carol	1992-04-18	32 years 1 mon 28 days	32
Dave	1988-01-30	36 years 4 mons 16 days	36
Eve	1995-12-05	28 years 6 mons 10 days	28

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

Operation	PostgreSQL	MySQL	SQL Server	Oracle
Days between	`date2 - date1`	`DATEDIFF(date2, date1)`	`DATEDIFF(DAY, date1, date2)`	`date2 - date1`
Months between	`EXTRACT(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 above	`FLOOR(MONTHS_BETWEEN / 12)`
Full interval	`AGE(d2, d1)`	Not available	Not available	Not 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_name	last_name	hire_date	years_employed	total_days
Dave	Wilson	2018-09-20	5	2095
Alice	Martin	2019-03-15	5	1918
Bob	Jones	2021-07-01	2	1080
Carol	Smith	2022-01-10	2	887
Eve	Turner	2026-02-14	0	122

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;

status	order_count	avg_days_to_ship	max_days_to_ship
delivered	2	3.0	3
shipped	1	4.0	4

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_id	iso_format	readable	european	full_format	time_12h
1001	2026-01-15	Jan 15, 2026	15/01/ 2026	Monday , January 15, 2026	09:30 AM

Common PostgreSQL format codes:

Code	Meaning	Example
`YYYY`	4-digit year	2026
`YY`	2-digit year	24
`MM`	Month number (01-12)	06
`Mon`	Abbreviated month	Jun
`Month`	Full month name	June
`DD`	Day of month (01-31)	15
`Day`	Full day name	Saturday
`Dy`	Abbreviated day	Sat
`HH24`	Hour 00-23	14
`HH12`	Hour 01-12	02
`MI`	Minute	30
`SS`	Second	45
`AM` / `PM`	AM/PM indicator	PM
`Q`	Quarter	2

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:

Code	Meaning	Example
`%Y`	4-digit year	2026
`%m`	Month number (01-12)	06
`%b`	Abbreviated month	Jun
`%M`	Full month name	June
`%d`	Day of month (01-31)	15
`%W`	Full day name	Saturday
`%H`	Hour 00-23	14
`%h`	Hour 01-12	02
`%i`	Minute	30
`%s`	Second	45
`%p`	AM/PM	PM

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;

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_date	year_start	month_start	week_start	day_start	hour_start
2026-06-05 11:00:00	2026-01-01 00:00:00	2026-06-01 00:00:00	2026-06-03 00:00:00	2026-06-05 00:00:00	2026-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;

month	orders	revenue
2026-01-01	1	155.97
2026-03-01	1	299.99
2026-06-01	4	455.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_start	order_count	revenue
2026-01-01	1	155.97
2026-02-01	0	0
2026-03-01	1	299.99
2026-04-01	0	0
2026-05-01	0	0
2026-06-01	4	455.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

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.

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

Task	PostgreSQL	MySQL	SQL Server
Current date	`CURRENT_DATE`	`CURDATE()`	`CAST(GETDATE() AS DATE)`
Current timestamp	`NOW()`	`NOW()`	`GETDATE()`
Extract year	`EXTRACT(YEAR FROM d)`	`YEAR(d)`	`YEAR(d)`
Extract month	`EXTRACT(MONTH FROM d)`	`MONTH(d)`	`MONTH(d)`
Add days	`d + INTERVAL '30 days'`	`DATE_ADD(d, INTERVAL 30 DAY)`	`DATEADD(DAY, 30, d)`
Subtract months	`d - INTERVAL '3 months'`	`DATE_SUB(d, INTERVAL 3 MONTH)`	`DATEADD(MONTH, -3, d)`
Days between	`d2 - d1`	`DATEDIFF(d2, d1)`	`DATEDIFF(DAY, d1, d2)`
Age in years	`EXTRACT(YEAR FROM AGE(d))`	`TIMESTAMPDIFF(YEAR, d, NOW())`	Complex formula
Truncate to month	`DATE_TRUNC('month', d)`	`DATE_FORMAT(d, '%Y-%m-01')`	`DATETRUNC(MONTH, d)` (2022+)
Format date	`TO_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.