Skip to content
Tutorials Center
My learning

Topics

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.