SQL date and time functions let you get the current date, pull out parts such as the year or month, add or subtract time, measure the gap between two dates, and group rows by day, week, or month, but the function names differ between PostgreSQL, MySQL, SQL Server, and SQLite.

Almost every real dataset has dates: when an order was placed, when a ticket was closed, when a user signed up. Questions like “how many orders did we get last month?” or “how long does shipping take?” all depend on handling dates correctly. This guide is for beginners and analysts who know basic SELECT, WHERE, and GROUP BY and want to work confidently with dates. Each section shows the idea first, then the syntax for the four most common databases, and a cheat sheet near the end puts them side by side.

How Databases Store Dates and Times

Most databases have separate types for dates and times:

TypeStoresExample
DATEA calendar date2026-03-15
TIMEA time of day14:30:00
TIMESTAMP / DATETIMEA date and a time2026-03-15 14:30:00
TIMESTAMP WITH TIME ZONEA moment in time, with time zone handling (PostgreSQL’s timestamptz)2026-03-15 14:30:00-08

Type names and behavior vary a little. For example, MySQL has both DATETIME and TIMESTAMP, and SQLite has no real date type, so it stores dates as text, numbers, or both.

Two rules save you a lot of trouble:

  1. Use real date types, not text. A date stored as '03/15/2026' in a text column will sort and compare incorrectly. Store dates in a date or timestamp column.
  2. Write date literals in ISO format: YYYY-MM-DD (and YYYY-MM-DD HH:MM:SS for timestamps). It is unambiguous and works in every database. 04/05/2026 could mean April 5 or May 4.

Sample Data for This Guide

The examples use a small orders table. In PostgreSQL, you can create it like this:

CREATE TABLE orders (
    order_id    INT,
    customer_id INT,
    order_date  DATE,
    shipped_at  TIMESTAMP,
    total       DECIMAL(10,2)
);

INSERT INTO orders VALUES
(1, 10, '2026-01-05', '2026-01-07 10:00:00', 120.00),
(2, 11, '2026-01-20', '2026-01-23 16:30:00',  80.50),
(3, 10, '2026-02-02', '2026-02-03 09:15:00', 200.00),
(4, 12, '2026-02-28', '2026-03-02 11:00:00',  45.25),
(5, 11, '2026-03-01', '2026-03-04 14:45:00', 310.00),
(6, 10, '2026-03-15', NULL,                   99.99);

Order 6 has not shipped yet, so its shipped_at is NULL. That detail matters later. For the other databases, the same table works with small changes to the types, such as DATETIME in MySQL and SQL Server, and text columns in SQLite.

Cheat Sheet: The Same Task in Four Databases

SQL has a standard for dates, but every database adds its own functions. Keep this table handy, and each section below explains the pieces.

TaskPostgreSQLMySQLSQL ServerSQLite
Today’s dateCURRENT_DATECURDATE()CAST(GETDATE() AS DATE)date('now')
Current date and timeNOW()NOW()GETDATE() or SYSDATETIME()datetime('now')
Add 7 daysd + 7 or d + INTERVAL '7 days'DATE_ADD(d, INTERVAL 7 DAY)DATEADD(day, 7, d)date(d, '+7 days')
Days between d1 and d2d2 - d1DATEDIFF(d2, d1)DATEDIFF(day, d1, d2)julianday(d2) - julianday(d1)
Get the yearEXTRACT(YEAR FROM d)YEAR(d)YEAR(d)strftime('%Y', d)
First day of the monthDATE_TRUNC('month', d)DATE_FORMAT(d, '%Y-%m-01')DATETRUNC(month, d) (newer versions) or DATEFROMPARTS(YEAR(d), MONTH(d), 1)date(d, 'start of month')
Last day of the month(DATE_TRUNC('month', d) + INTERVAL '1 month' - INTERVAL '1 day')::dateLAST_DAY(d)EOMONTH(d)date(d, 'start of month', '+1 month', '-1 day')
Format as textTO_CHAR(d, 'YYYY-MM-DD')DATE_FORMAT(d, '%Y-%m-%d')FORMAT(d, 'yyyy-MM-dd')strftime('%Y-%m-%d', d)

The examples below use PostgreSQL syntax first, and then show the other databases where they differ.

Getting the Current Date and Time

-- PostgreSQL
SELECT CURRENT_DATE;      -- today's date
SELECT NOW();             -- current date and time
-- MySQL
SELECT CURDATE(), NOW();

-- SQL Server
SELECT CAST(GETDATE() AS DATE), GETDATE();

-- SQLite
SELECT date('now'), datetime('now');

A common use is a filter like “orders from the last 30 days”:

SELECT order_id, order_date
FROM orders
WHERE order_date >= CURRENT_DATE - 30;

In PostgreSQL, subtracting a whole number from a DATE subtracts days. In other databases, use DATE_SUB, DATEADD, or SQLite’s date('now', '-30 days').

One caution: “now” changes every time you run the query. If you need a repeatable report, use a fixed date instead of the current date.

Extracting Parts of a Date

You often need just the year, month, or day:

SELECT
    order_id,
    EXTRACT(YEAR  FROM order_date) AS order_year,
    EXTRACT(MONTH FROM order_date) AS order_month,
    EXTRACT(DAY   FROM order_date) AS order_day
FROM orders;

For 2026-03-15, the three columns return 2026, 3, and 15. Other fields you can extract include the quarter, the week, and the day of the week (DOW in PostgreSQL, where Sunday is 0).

In MySQL and SQL Server, YEAR(d), MONTH(d), and DAY(d) do the same job. SQL Server also has DATEPART(week, d) for other parts. In SQLite, use strftime('%Y', d), strftime('%m', d), and so on, and note that strftime returns text.

Adding and Subtracting Time

-- PostgreSQL
SELECT DATE '2026-03-15' + 7;                      -- 2026-03-22
SELECT DATE '2026-03-15' + INTERVAL '7 days';      -- 2026-03-22 00:00:00
SELECT DATE '2026-03-15' - INTERVAL '1 month';     -- 2026-02-15 00:00:00

Adding an integer to a DATE returns a date. Adding an INTERVAL returns a timestamp, so cast back with ::date if you want only the date.

-- MySQL
SELECT DATE_ADD('2026-03-15', INTERVAL 7 DAY);
SELECT DATE_SUB('2026-03-15', INTERVAL 1 MONTH);

-- SQL Server
SELECT DATEADD(day, 7, '2026-03-15');
SELECT DATEADD(month, -1, '2026-03-15');

-- SQLite
SELECT date('2026-03-15', '+7 days');
SELECT date('2026-03-15', '-1 month');

Month math can surprise you. What is one month after January 31? Different databases handle it differently, so test month-end dates when it matters.

Finding the Difference Between Two Dates

How many days passed between two dates? In PostgreSQL, subtracting two DATE values gives an integer number of days:

SELECT DATE '2026-03-15' - DATE '2026-03-01';   -- 14

To find how long each order took to ship, remember that shipped_at is a timestamp and order_date is a date. Cast the timestamp to a date first:

SELECT
    order_id,
    shipped_at::date - order_date AS days_to_ship
FROM orders
WHERE shipped_at IS NOT NULL;
order_iddays_to_ship
12
23
31
42
53

Without the cast, subtracting a date from a timestamp returns an interval (for example, 2 days 10:00:00), not a plain number of days.

The other databases use functions:

-- MySQL: DATEDIFF(end, start)
SELECT order_id, DATEDIFF(shipped_at, order_date) AS days_to_ship FROM orders;

-- SQL Server: DATEDIFF(unit, start, end)
SELECT order_id, DATEDIFF(day, order_date, shipped_at) AS days_to_ship FROM orders;

-- SQLite
SELECT order_id,
       CAST(julianday(shipped_at) - julianday(order_date) AS INTEGER) AS days_to_ship
FROM orders;

Watch the argument order. MySQL’s DATEDIFF(a, b) is a - b, but SQL Server’s DATEDIFF(day, a, b) is b - a. Swapping them flips the sign.

To measure hours or minutes, work with timestamps. In PostgreSQL:

SELECT EXTRACT(EPOCH FROM (TIMESTAMP '2026-03-15 14:30:00' - TIMESTAMP '2026-03-15 09:00:00')) / 3600;
-- 5.5 hours

PostgreSQL also has AGE(a, b), which returns an interval in years, months, and days. For example, EXTRACT(YEAR FROM AGE(CURRENT_DATE, birth_date)) gives a person’s age in whole years.

Grouping by Month, Week, or Year

Reports often group by a period. The tool for this is “truncating” a date to the start of its month, week, or year.

-- PostgreSQL
SELECT
    DATE_TRUNC('month', order_date)::date AS month,
    SUM(total) AS revenue
FROM orders
GROUP BY 1
ORDER BY 1;
monthrevenue
2026-01-01200.50
2026-02-01245.25
2026-03-01409.99

DATE_TRUNC also accepts 'week', 'quarter', 'year', 'day', and 'hour'. Because the result is a real date, it sorts correctly and works in charts.

In SQLite, strftime('%Y-%m', order_date) gives 2026-01, 2026-02, and 2026-03. In MySQL, DATE_FORMAT(order_date, '%Y-%m-01') does the same job. In SQL Server 2022 and later, DATETRUNC(month, order_date) is available. In older versions, use DATEFROMPARTS(YEAR(order_date), MONTH(order_date), 1).

A common mistake is grouping only by month number, EXTRACT(MONTH FROM order_date). That puts January 2025 and January 2026 in the same group. Group by year and month together, or truncate the date.

Month-Over-Month Change

Combine date truncation with a window function to compare each month with the previous one. See SQL window functions for how LAG works.

WITH monthly AS (
    SELECT
        DATE_TRUNC('month', order_date)::date AS month,
        SUM(total) AS revenue
    FROM orders
    GROUP BY 1
)
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS previous_revenue,
    ROUND(
        100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
        / LAG(revenue) OVER (ORDER BY month), 1
    ) AS pct_change
FROM monthly
ORDER BY month;
monthrevenueprevious_revenuepct_change
2026-01-01200.50
2026-02-01245.25200.5022.3
2026-03-01409.99245.2567.2

The first month has no previous month, so those values are NULL.

Filtering by Date Range

Date filters are where most date bugs live. Use this pattern: start is inclusive, end is exclusive.

-- Orders placed in February 2026
SELECT order_id
FROM orders
WHERE order_date >= '2026-02-01'
  AND order_date <  '2026-03-01';

This returns orders 3 and 4. The end value is the first day of the next month, so you never have to work out whether the month has 28, 30, or 31 days.

Be Careful With BETWEEN and Timestamps

BETWEEN includes both ends, which works for dates but fails for timestamps. A timestamp written as '2026-03-04' means midnight at the start of that day:

-- Looks like "shipped March 1 to March 4", but it isn't
SELECT order_id
FROM orders
WHERE shipped_at BETWEEN '2026-03-01' AND '2026-03-04';

This returns only order 4. Order 5 shipped at 2026-03-04 14:45, which is after midnight on March 4, so it is left out. The safe version uses the half-open pattern:

SELECT order_id
FROM orders
WHERE shipped_at >= '2026-03-01'
  AND shipped_at <  '2026-03-05';

This returns orders 4 and 5, as intended.

Do Not Wrap the Column in a Function

This looks correct but can be slow on a large table:

-- Slow pattern: the function runs on every row and blocks index use
SELECT order_id FROM orders WHERE EXTRACT(YEAR FROM order_date) = 2026;

Compare the raw column to a range instead, so the database can use an index:

SELECT order_id
FROM orders
WHERE order_date >= '2026-01-01'
  AND order_date <  '2027-01-01';

For more on why queries slow down and how to fix them, see why your SQL query is slow.

Formatting and Parsing Dates

Use formatting for display and parsing to convert text into dates.

-- PostgreSQL
SELECT TO_CHAR(order_date, 'YYYY-MM-DD');
SELECT TO_CHAR(order_date, 'Month DD, YYYY');
SELECT TO_DATE('15/03/2026', 'DD/MM/YYYY');

-- MySQL
SELECT DATE_FORMAT(order_date, '%Y-%m-%d');
SELECT STR_TO_DATE('15/03/2026', '%d/%m/%Y');

-- SQL Server
SELECT FORMAT(order_date, 'yyyy-MM-dd');
SELECT CONVERT(date, '15/03/2026', 103);

-- SQLite
SELECT strftime('%Y-%m-%d', order_date);

Format strings differ in every database (YYYY versus %Y versus yyyy), so look them up for yours. A good habit is to do your analysis on real dates and format only at the very end, since formatted values are text and sort alphabetically.

When cleaning messy date text, such as mixed formats or impossible dates, see cleaning messy data with pure SQL.

Time Zones

Time zones are a common source of off-by-one-day errors.

  • Store timestamps in UTC, and convert to local time when you display or group them. In PostgreSQL, timestamptz does this tracking for you.
  • Group by the time zone your business uses. An order placed at 11 PM Pacific on March 31 is April 1 in UTC. If you group by UTC date, it lands in the wrong month for a California business.
  • Know your server’s time zone. NOW() returns the database server’s idea of the time, which may not be yours.

In PostgreSQL, AT TIME ZONE converts a timestamp:

SELECT shipped_at AT TIME ZONE 'UTC' AT TIME ZONE 'America/Los_Angeles' FROM orders;

The exact behavior depends on whether the column is timestamp or timestamptz, so read your database’s documentation before relying on conversions.

NULL Dates

A NULL date means “unknown” or “not yet”. Any arithmetic with a NULL returns NULL, and comparisons with NULL are never true:

-- Orders that have not shipped yet
SELECT order_id FROM orders WHERE shipped_at IS NULL;

Use IS NULL and IS NOT NULL, not = NULL. Use COALESCE(shipped_at, NOW()) if you want to treat unshipped orders as “still in transit as of now”, such as when measuring waiting time.

Common Mistakes to Avoid

  • Storing dates as text. You lose date math and correct sorting.
  • Using BETWEEN on timestamps. The end of the range cuts off most of the last day.
  • Wrapping an indexed date column in a function in the WHERE clause.
  • Grouping by month number without the year.
  • Mixing a date and a timestamp in a subtraction, and getting an interval where you expected a number.
  • Swapping the order of arguments in DATEDIFF.
  • Ignoring time zones, so late-night events land in the wrong day.
  • Assuming one database’s function exists in another. DATE_TRUNC, DATEDIFF, and strftime are not universal.
  • Hardcoding dates in a report that should update, or using “now” in a report that should be repeatable.

Frequently Asked Questions

What is the difference between DATE, DATETIME, and TIMESTAMP?

DATE stores only a calendar date. DATETIME and TIMESTAMP store a date and a time. In some databases, such as MySQL, TIMESTAMP also converts to and from UTC, while DATETIME does not. In PostgreSQL, timestamp has no time zone and timestamptz does.

How do I get today’s date in SQL?

In PostgreSQL, use CURRENT_DATE. In MySQL, use CURDATE(). In SQL Server, use CAST(GETDATE() AS DATE). In SQLite, use date('now').

How do I calculate the number of days between two dates?

In PostgreSQL, subtract the dates (d2 - d1). In MySQL, use DATEDIFF(d2, d1). In SQL Server, use DATEDIFF(day, d1, d2). In SQLite, subtract their julianday values.

How do I group data by month?

Truncate the date to the first of its month, with DATE_TRUNC('month', d) in PostgreSQL or the equivalent in your database, and group by the result. That keeps different years separate.

Why does my date filter miss rows on the last day?

You are probably using BETWEEN with a timestamp column. A timestamp of '2026-03-04' means midnight, so rows later that day are excluded. Use >= for the start and < for the day after the end.

Do date functions work the same in every database?

No. The ideas are the same, but the function names, argument order, and format strings differ. Use the cheat sheet in this guide and check your database’s documentation.

Conclusion

Date work in SQL comes down to a few habits: store real date types, write dates in ISO format, truncate to group by period, use a start-inclusive, end-exclusive range for filters, and keep an eye on time zones and NULLs. Once these are second nature, questions like “revenue by month,” “average days to ship,” and “customers who haven’t ordered in 90 days” become short, readable queries. Practice on a small table like the one in this guide, and keep the cheat sheet nearby when you switch databases. If you need help with data analysis or reporting, you can book a free consultation.

The PostgreSQL-style examples were run with the sample data in a PostgreSQL-compatible engine, and the SQLite examples in SQLite. The MySQL and SQL Server examples follow those databases’ documented syntax. Function names and behavior vary by database version, so check your own database’s documentation before using them in production.