Why do DATEADD and DATE_ADD differ?

They are different vendor implementations of the same idea. SQL Server uses DATEADD(unit, n, date), MySQL uses DATE_ADD(date, INTERVAL n unit) and PostgreSQL uses plain interval arithmetic like date + INTERVAL '7 days'. The SQL standard never settled one spelling.

Does NOW() return UTC or local time?

It depends on the engine and session settings. PostgreSQL now() respects the session time zone, MySQL NOW() uses the server time zone, and SQLite datetime('now') returns UTC unless you add the 'localtime' modifier.

How do I truncate a date to the start of the month?

PostgreSQL has DATE_TRUNC('month', d) and SQL Server 2022+ has DATETRUNC(MONTH, d). MySQL has no equivalent, so you format to a fixed-day string like DATE_FORMAT(d, '%Y-%m-01'), and SQLite uses date(d, 'start of month').

Why does day-of-week return different numbers?

The numbering convention is not standard. MySQL DAYOFWEEK returns 1-7 with Sunday as 1, PostgreSQL and SQLite return 0-6 with Sunday as 0, and SQL Server depends on the SET DATEFIRST setting. Always normalise before comparing across engines.

Is anything sent to a server?

No. This is a static reference rendered entirely in your browser. There is no database connection and nothing you type is transmitted or stored.

What is the SQL Date/Time Functions Reference?

Cross-dialect SQL date/time function reference covering NOW, DATEADD, EXTRACT, DATE_TRUNC and format functions, with side-by-side syntax for MySQL, PostgreSQL, SQLite and SQL Server. It runs free in your browser on Gera Tools, with nothing uploaded.

SQL Date/Time Functions Reference

Name: SQL Date/Time Functions Reference
Creator: Gera Tools
License: https://creativecommons.org/licenses/by/4.0/

One concept, four spellings

Date and time handling is where SQL portability breaks down fastest. Every major engine has its own function names, argument order and timezone defaults for the same logical operations. This reference puts the common tasks — current time, adding intervals, extracting fields, truncating and formatting — side by side across MySQL, PostgreSQL, SQLite and SQL Server so you can translate at a glance.

Common date tasks — cross-dialect comparison

Get the current timestamp

NOW()                          -- MySQL, PostgreSQL
GETDATE()                      -- SQL Server (local time)
SYSUTCDATETIME()               -- SQL Server (UTC)
datetime('now')                -- SQLite (UTC)
datetime('now', 'localtime')   -- SQLite (local)

Add an interval

DATE_ADD(d, INTERVAL 7 DAY)   -- MySQL
d + INTERVAL '7 days'         -- PostgreSQL
date(d, '+7 days')            -- SQLite (returns text)
DATEADD(DAY, 7, d)            -- SQL Server

Watch the argument order: SQL Server puts the unit first and the count second.

Truncate to the start of the month

DATE_FORMAT(d, '%Y-%m-01')            -- MySQL (returns string; CAST if needed)
DATE_TRUNC('month', d)                -- PostgreSQL (returns timestamp)
date(d, 'start of month')             -- SQLite (returns text)
DATETRUNC(MONTH, d)                   -- SQL Server 2022+
DATEFROMPARTS(YEAR(d),MONTH(d),1)    -- SQL Server (pre-2022)

Extract the year or day-of-week

YEAR(d)              -- MySQL / SQL Server
EXTRACT(YEAR FROM d) -- PostgreSQL / MySQL / SQL Server 2022+
strftime('%Y', d)    -- SQLite (always returns text)

DAYOFWEEK(d)    -- MySQL: 1=Sunday … 7=Saturday
EXTRACT(DOW FROM d) -- PostgreSQL: 0=Sunday … 6=Saturday
strftime('%w', d)   -- SQLite: 0=Sunday … 6=Saturday
DATEPART(dw, d)     -- SQL Server: depends on SET DATEFIRST

Conventions that bite

Day-of-week numbering is the classic portability trap: Sunday is 1 in MySQL, 0 in PostgreSQL and SQLite, and configurable in SQL Server via SET DATEFIRST. If you are comparing day-of-week across engines, normalise to 0–6 explicitly.

Return types differ. SQLite’s strftime always returns a text string, not a date or number. Wrap in CAST(strftime('%Y', d) AS INTEGER) before doing arithmetic on the result. PostgreSQL’s DATE_TRUNC returns a timestamp, which is fine for comparisons but can truncate precision unexpectedly if you cast it directly to date.

The year 2038 problem lives in SQL Server. DATEDIFF(SECOND, '1970-01-01', d) overflows a 32-bit INT for dates beyond roughly January 2038. Use DATEDIFF_BIG there to get a 64-bit result.

Timezone defaults vary by session. PostgreSQL NOW() respects the session time zone set by SET TIME ZONE. MySQL NOW() uses the server’s time_zone system variable. SQLite datetime('now') is always UTC regardless of the host OS. When storing timestamps, prefer UTC everywhere and convert at display time.

Practical patterns for common date tasks

Calculate age in years (birthday check)

-- PostgreSQL
EXTRACT(YEAR FROM AGE(NOW(), birth_date))

-- MySQL
TIMESTAMPDIFF(YEAR, birth_date, CURDATE())

-- SQL Server
DATEDIFF(YEAR, birth_date, GETDATE())
  - CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, birth_date, GETDATE()), birth_date) > GETDATE() THEN 1 ELSE 0 END

-- SQLite
CAST((julianday('now') - julianday(birth_date)) / 365.25 AS INTEGER)

Generate the first day of the current month

DATE_FORMAT(NOW(), '%Y-%m-01')         -- MySQL
DATE_TRUNC('month', NOW())             -- PostgreSQL
date(date('now'), 'start of month')    -- SQLite
DATETRUNC(MONTH, GETDATE())            -- SQL Server 2022+

Check if a date is within the last 30 days

WHERE created_at >= NOW() - INTERVAL 30 DAY       -- MySQL
WHERE created_at >= NOW() - INTERVAL '30 days'    -- PostgreSQL
WHERE created_at >= date('now', '-30 days')       -- SQLite
WHERE created_at >= DATEADD(DAY, -30, GETDATE())  -- SQL Server

These patterns cover the most common date-range filters in analytics and reporting queries. Always index the date column being filtered — range scans on unindexed timestamp columns in large tables are a frequent performance problem.