SQL Reference ยท Dialects and Concepts
SQL date functions across dialects
Map of SQL date and time functions across SQLite, PostgreSQL, MySQL, SQL Server and Oracle: current time, add and subtract, differences, extract parts, format.
Dates are the area where SQL dialects differ most. Getting today's date, adding a month, finding the days between two dates, pulling out the year and formatting a date as text each have a different spelling in each system. SQLite is the odd one out: it has no date type at all, only functions that work on text (ISO 8601 strings), Julian day numbers and Unix timestamps.
The standard offers CURRENT_DATE, CURRENT_TIMESTAMP, EXTRACT(field FROM x), INTERVAL arithmetic and DATE '2025-01-31' literals. PostgreSQL follows it closely, Oracle partly, and the others much less.
SELECT date('now') AS today, strftime('%Y', '2025-03-09') AS year;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Dialect differences
| Operation | SQLite | PostgreSQL | MySQL | SQL Server | Oracle |
|---|---|---|---|---|---|
| Today's date | date('now') |
CURRENT_DATE |
CURDATE() |
CAST(GETDATE() AS DATE) |
TRUNC(SYSDATE) |
| Current date and time | datetime('now') |
now() |
NOW() |
GETDATE(), SYSDATETIME() |
SYSTIMESTAMP, SYSDATE |
| Add an interval | date(x, '+3 days') |
x + INTERVAL '3 days' |
DATE_ADD(x, INTERVAL 3 DAY) |
DATEADD(day, 3, x) |
x + 3, ADD_MONTHS(x, n) |
| Difference in days | julianday(a) - julianday(b) |
a - b (dates) |
DATEDIFF(a, b) |
DATEDIFF(day, b, a) |
a - b |
| Extract a part | strftime('%Y', x) |
EXTRACT(YEAR FROM x) |
YEAR(x), EXTRACT(...) |
YEAR(x), DATEPART(year, x) |
EXTRACT(YEAR FROM x) |
| Format as text | strftime(fmt, x) |
TO_CHAR(x, 'YYYY-MM') |
DATE_FORMAT(x, '%Y-%m') |
FORMAT(x, 'yyyy-MM') |
TO_CHAR(x, 'YYYY-MM') |
| Parse text | date(text) |
TO_DATE(text, fmt), '2025-01-31'::date |
STR_TO_DATE(text, fmt) |
CONVERT(date, text, style) |
TO_DATE(text, fmt) |
| Truncate to month | date(x, 'start of month') |
date_trunc('month', x) |
DATE_FORMAT(x, '%Y-%m-01') |
DATETRUNC(month, x) (2022+) |
TRUNC(x, 'MM') |
| Last day of month | date(x, 'start of month', '+1 month', '-1 day') |
date_trunc(...) + INTERVAL '1 month - 1 day' |
LAST_DAY(x) |
EOMONTH(x) |
LAST_DAY(x) |
Note the argument order of the difference functions. MySQL's two-argument DATEDIFF(a, b) is a - b in days. SQL Server's is DATEDIFF(unit, start, end), the reverse direction. This is a classic source of sign errors.
-- PostgreSQL SELECT CURRENT_DATE + 30 AS in_30_days, DATE '2025-03-01' - DATE '2025-01-31' AS days_between, EXTRACT(YEAR FROM TIMESTAMP '2025-03-09 14:30') AS year_value, TO_CHAR(DATE '2025-03-09', 'DD Mon YYYY') AS formatted; -- MySQL SELECT DATE_ADD(CURDATE(), INTERVAL 30 DAY), DATEDIFF('2025-03-01', '2025-01-31'), DATE_FORMAT('2025-03-09', '%d %b %Y'); -- SQL Server SELECT DATEADD(day, 30, CAST(GETDATE() AS DATE)), DATEDIFF(day, '2025-01-31', '2025-03-01'), FORMAT(CAST('2025-03-09' AS DATE), 'dd MMM yyyy'); -- Oracle SELECT TRUNC(SYSDATE) + 30, DATE '2025-03-01' - DATE '2025-01-31', TO_CHAR(DATE '2025-03-09', 'DD Mon YYYY') FROM dual;
For the dates above, the difference is 29 days in every dialect.
Usage notes
- SQLite stores dates as text. Store them as
'YYYY-MM-DD'or'YYYY-MM-DD HH:MM:SS'; that format sorts correctly and works with every date function. Other formats ('03/09/2025') are not understood and the functions returnNULL. - Month arithmetic clamps differently. Adding a month to January 31 gives a nonexistent February 31. SQLite normalizes it to the next valid day (March 3 in a non-leap year), PostgreSQL and MySQL clamp to the end of February, and Oracle's
ADD_MONTHSreturns the last day of the month. Check results for end-of-month dates. - Time zones.
now()andCURRENT_TIMESTAMPare UTC in SQLite, session-time-zone aware in PostgreSQL, and server-local in SQL ServerGETDATE(). Store UTC and convert at the edge. - Do not wrap indexed columns in functions.
WHERE strftime('%Y', ordered_at) = '2025'cannot use an index onordered_at; writeWHERE ordered_at >= '2025-01-01' AND ordered_at < '2026-01-01'. - Use half-open ranges (
>= start AND < next_start) for timestamps, so the last second of a day is never lost. - Prefer real date types (
DATE,TIMESTAMP,DATETIME2) in databases that have them; avoid storing dates as strings or integers. - Oracle
DATEincludes a time down to the second;SYSDATEis not a pure date.