(opens in a new tab)

Date Functions in SQL: MySQL, PostgreSQL, SQL Server

August 14, 2026 by Chat2DB Team

Date functions in SQL are where dialect differences bite hardest. The SQL standard defines only a small core (CURRENT_DATE, CURRENT_TIMESTAMP, EXTRACT), and every major engine layers its own functions on top: MySQL has DATE_ADD and DATE_FORMAT, PostgreSQL has intervals, AGE and TO_CHAR, and SQL Server has GETDATE, DATEADD, DATEDIFF and FORMAT. Code that runs on one engine frequently fails — or worse, silently returns different results — on another.

This guide covers the operations you actually need day to day: getting the current date and time, extracting parts, doing arithmetic, computing differences, formatting, and truncating, with runnable examples for all three engines and a comparison table you can keep as a reference.

All examples use this sample table. The inserts below are valid on MySQL, PostgreSQL, and SQL Server:

CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    customer_id INT NOT NULL,
    ordered_at  TIMESTAMP NOT NULL,   -- use DATETIME2 on SQL Server
    shipped_at  TIMESTAMP NULL,
    amount      DECIMAL(10, 2) NOT NULL
);
 
INSERT INTO orders (order_id, customer_id, ordered_at, shipped_at, amount) VALUES
(1, 101, '2026-07-28 09:15:00', '2026-07-30 14:00:00', 149.90),
(2, 102, '2026-08-01 18:40:00', '2026-08-02 08:30:00',  89.00),
(3, 101, '2026-08-05 11:05:00', NULL,                  240.50),
(4, 103, '2026-08-13 23:59:00', '2026-08-14 10:15:00',  35.25);

Current Date and Time

Every engine has a "now" function; the names differ:

-- Standard SQL (works everywhere)
SELECT CURRENT_TIMESTAMP;   -- date + time
SELECT CURRENT_DATE;        -- date only (not in SQL Server)
 
-- MySQL
SELECT NOW(), CURDATE(), CURTIME(), UTC_TIMESTAMP();
 
-- PostgreSQL
SELECT NOW(), CURRENT_DATE, LOCALTIMESTAMP, NOW() AT TIME ZONE 'UTC';
 
-- SQL Server
SELECT GETDATE(), SYSDATETIME(), GETUTCDATE(), CAST(GETDATE() AS DATE);

Two details worth knowing:

• PostgreSQL's NOW() is fixed per transaction. It returns the transaction start time, so multiple calls in one transaction return the identical value. Use clock_timestamp() when you need the actual wall-clock moment.
• SQL Server has no CURRENT_DATE. The idiom is CAST(GETDATE() AS DATE), or SYSDATETIME() when you need datetime2 precision.

Extracting Parts of a Date

The standard function is EXTRACT(part FROM value), supported by MySQL and PostgreSQL. SQL Server uses DATEPART(part, value) plus the shortcuts YEAR(), MONTH(), DAY() (which MySQL also supports).

-- MySQL and PostgreSQL
SELECT
    order_id,
    EXTRACT(YEAR  FROM ordered_at) AS order_year,
    EXTRACT(MONTH FROM ordered_at) AS order_month,
    EXTRACT(DOW   FROM ordered_at) AS weekday   -- PostgreSQL only; MySQL: DAYOFWEEK(ordered_at)
FROM orders;
 
-- SQL Server
SELECT
    order_id,
    DATEPART(year,    ordered_at) AS order_year,
    DATEPART(month,   ordered_at) AS order_month,
    DATEPART(weekday, ordered_at) AS weekday
FROM orders;

Expected output for order 2 (2026-08-01 18:40:00, a Saturday): order_year = 2026, order_month = 8. Weekday numbering is a notorious trap: PostgreSQL's DOW returns 0–6 with Sunday = 0, MySQL's DAYOFWEEK returns 1–7 with Sunday = 1, and SQL Server's weekday depends on the session's DATEFIRST setting. Never compare weekday numbers across engines without checking the convention; for readable output use DAYNAME() (MySQL), TO_CHAR(ordered_at, 'Day') (PostgreSQL), or DATENAME(weekday, ordered_at) (SQL Server).

Date Arithmetic: Adding and Subtracting Intervals

Adding "one month" or "30 days" is spelled differently everywhere:

-- MySQL
SELECT DATE_ADD(ordered_at, INTERVAL 7 DAY)   AS due_date,
       DATE_SUB(ordered_at, INTERVAL 1 MONTH) AS month_before
FROM orders WHERE order_id = 1;
 
-- PostgreSQL: plain operators with INTERVAL literals
SELECT ordered_at + INTERVAL '7 days'  AS due_date,
       ordered_at - INTERVAL '1 month' AS month_before
FROM orders WHERE order_id = 1;
 
-- SQL Server
SELECT DATEADD(day, 7, ordered_at)    AS due_date,
       DATEADD(month, -1, ordered_at) AS month_before
FROM orders WHERE order_id = 1;

All three return due_date = 2026-08-04 09:15:00 and month_before = 2026-06-28 09:15:00 for order 1. Month arithmetic clamps at month ends: adding one month to January 31 yields February 28 (or 29) on all three engines, which means DATE_ADD is not reversible — subtracting the month back gives February 28 minus a month, not January 31.

Date Differences

-- MySQL: DATEDIFF returns whole days (date1 - date2)
SELECT order_id, DATEDIFF(shipped_at, ordered_at) AS days_to_ship
FROM orders WHERE shipped_at IS NOT NULL;
 
-- MySQL: for other units use TIMESTAMPDIFF
SELECT order_id, TIMESTAMPDIFF(HOUR, ordered_at, shipped_at) AS hours_to_ship
FROM orders WHERE shipped_at IS NOT NULL;
 
-- PostgreSQL: subtracting timestamps yields an interval; AGE gives calendar parts
SELECT order_id,
       shipped_at - ordered_at        AS ship_interval,
       AGE(shipped_at, ordered_at)    AS ship_age,
       EXTRACT(EPOCH FROM (shipped_at - ordered_at)) / 3600 AS hours_to_ship
FROM orders WHERE shipped_at IS NOT NULL;
 
-- SQL Server: DATEDIFF(unit, start, end) counts boundary crossings
SELECT order_id, DATEDIFF(hour, ordered_at, shipped_at) AS hours_to_ship
FROM orders WHERE shipped_at IS NOT NULL;

For order 4 (ordered 23:59, shipped 10:15 next day) the true elapsed time is 10 hours 16 minutes. Note the semantic difference: SQL Server's DATEDIFF counts crossed boundaries, not elapsed time. DATEDIFF(day, '2026-08-13 23:59', '2026-08-14 00:01') returns 1 even though only two minutes elapsed. MySQL's DATEDIFF similarly compares the date parts only. PostgreSQL's subtraction gives an exact interval (10:16:00), which is usually what you want for durations; AGE additionally normalizes into years/months/days.

Formatting Dates as Strings

-- MySQL
SELECT DATE_FORMAT(ordered_at, '%Y-%m-%d %H:%i') AS formatted
FROM orders WHERE order_id = 2;
-- => '2026-08-01 18:40'
 
-- PostgreSQL
SELECT TO_CHAR(ordered_at, 'YYYY-MM-DD HH24:MI') AS formatted
FROM orders WHERE order_id = 2;
-- => '2026-08-01 18:40'
 
-- SQL Server
SELECT FORMAT(ordered_at, 'yyyy-MM-dd HH:mm') AS formatted
FROM orders WHERE order_id = 2;
-- => '2026-08-01 18:40'

Each engine uses a different pattern language: MySQL uses %-codes, PostgreSQL uses Oracle-style templates (YYYY, MI for minutes — MM is months), and SQL Server's FORMAT uses .NET patterns and is case-sensitive (MM months vs mm minutes). SQL Server's FORMAT is also markedly slower than CONVERT(varchar, ordered_at, 120); avoid it in queries over millions of rows. Format only at the presentation layer — storing formatted strings in the database forfeits sorting, arithmetic, and index range scans.

Truncating Dates

Truncation snaps a timestamp down to the start of a period — essential for grouping by month or day:

-- PostgreSQL (and MySQL 8.0+ has no direct equivalent; see below)
SELECT DATE_TRUNC('month', ordered_at) AS month_start,
       SUM(amount)                     AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', ordered_at)
ORDER BY month_start;

Expected output:

Equivalents on the other engines:

-- MySQL: rebuild the month start, or format it
SELECT DATE_FORMAT(ordered_at, '%Y-%m-01') AS month_start, SUM(amount) AS revenue
FROM orders GROUP BY month_start ORDER BY month_start;
 
-- SQL Server 2022+: DATETRUNC
SELECT DATETRUNC(month, ordered_at) AS month_start, SUM(amount) AS revenue
FROM orders GROUP BY DATETRUNC(month, ordered_at);
 
-- SQL Server before 2022: the classic idiom
SELECT DATEFROMPARTS(YEAR(ordered_at), MONTH(ordered_at), 1) AS month_start, SUM(amount) AS revenue
FROM orders GROUP BY DATEFROMPARTS(YEAR(ordered_at), MONTH(ordered_at), 1);

Cross-Dialect Comparison Table

Common Pitfalls

Time zones. MySQL's TIMESTAMP converts to UTC on write and back to the session time zone on read, while DATETIME stores the literal value; PostgreSQL's timestamptz stores UTC and renders in the session's TimeZone, while timestamp is zone-naive; SQL Server's datetime2 is naive and datetimeoffset carries an offset. Mixing naive and zone-aware values is the leading cause of "my report is off by one day" bugs — pick UTC storage and convert at the edges.

Implicit casts kill indexes. A predicate like WHERE DATE(ordered_at) = '2026-08-05' or WHERE CAST(ordered_at AS DATE) = '2026-08-05' wraps the column in a function, so a B-tree index on ordered_at cannot be used for a range seek on most engines. Rewrite as a half-open range, which is index-friendly everywhere:

SELECT * FROM orders
WHERE ordered_at >= '2026-08-05'
  AND ordered_at <  '2026-08-06';

String-to-date guessing. Passing '05/08/2026' and letting the engine guess the format depends on session settings (DATEFORMAT in SQL Server, locale in MySQL). Always parse explicitly with STR_TO_DATE, TO_DATE, or a style number in CONVERT.

Boundary-counting differences. As shown above, DATEDIFF in SQL Server and MySQL is not "elapsed time." For SLAs and durations, compute in seconds or use interval subtraction.

When you work across several engines at once, running the same query side by side is the fastest way to catch these discrepancies; a multi-dialect client such as Chat2DB (opens in a new tab) lets you keep MySQL, PostgreSQL and SQL Server connections open in one workspace and compare results directly.

Conclusion

The mental model that transfers across engines: get "now" from one canonical function, store timestamps in UTC, extract with EXTRACT/DATEPART, add with intervals or DATEADD, measure durations in exact units rather than boundary counts, group with truncation rather than formatting, and format only for display. Keep the comparison table above at hand and most cross-dialect date bugs become mechanical translation instead of debugging sessions.