SQL Date Difference: DATEDIFF by Database

By Sharon Ben-Moshe · August 26, 2026 · 2 min read

Sharon Ben-Moshe is the founder of sqlfmt, a browser-based SQL formatter and validator with misspelled-keyword suggestions across PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery.

Calculate date differences in SQL Server, MySQL, PostgreSQL, SQLite, and BigQuery. Learn syntax, argument order, timestamp behavior, and boundary rules.

Quick answer

DATEDIFF is not portable SQL. SQL Server counts date-part boundaries, MySQL returns days, PostgreSQL subtracts date or timestamp values, SQLite commonly subtracts julianday values, and BigQuery uses DATE_DIFF. Write the date type and argument order explicitly for each database.

FAQs

Is DATEDIFF the same in every SQL database?

No. SQL Server DATEDIFF counts date-part boundaries crossed, MySQL DATEDIFF returns a day difference, and PostgreSQL does not use the same function for ordinary date subtraction. SQLite and BigQuery also use different documented functions or expressions.

Why is the argument order important in date difference SQL?

The order determines the sign of the result and differs by database. SQL Server uses DATEDIFF(part, start, end), while MySQL DATEDIFF and BigQuery DATE_DIFF use end then start. Verify the target dialect before copying a query.

How do I calculate days between two dates in PostgreSQL?

Subtract one date from another: end_date - start_date. For timestamp values, PostgreSQL returns an interval instead, so choose the date or timestamp operation that matches the intended result.

Should I use DATEDIFF for timestamps?

Only after checking what the database counts. In SQL Server, DATEDIFF counts boundaries for the chosen unit. If the requirement is elapsed duration, use the appropriate timestamp operation and account for time zones and precision explicitly.

SQL Dialects