Skip to content

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.

SQL
SELECT date('now') AS today, strftime('%Y', '2025-03-09') AS year;

Try it

SQL Date arithmetic in SQLite ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL Extracting parts and grouping by month ยท database: shop

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.

SQL
-- 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 return NULL.
  • 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_MONTHS returns the last day of the month. Check results for end-of-month dates.
  • Time zones. now() and CURRENT_TIMESTAMP are UTC in SQLite, session-time-zone aware in PostgreSQL, and server-local in SQL Server GETDATE(). Store UTC and convert at the edge.
  • Do not wrap indexed columns in functions. WHERE strftime('%Y', ordered_at) = '2025' cannot use an index on ordered_at; write WHERE 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 DATE includes a time down to the second; SYSDATE is not a pure date.