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.
Keep reading
The Comprehensive Guide to SQL Data Types: Storage, Precision, and Cross-Dialect Portability
By Sharon Ben-Moshe · Oct 13, 2026
The complete guide to SQL data types across PostgreSQL, MySQL, SQL Server, and SQLite. Numeric precision, character sets, timestamps, and JSON.
Read postThe Definitive Guide to SQL Window Functions: Syntax, Framing, and Real-World Recipes
By Sharon Ben-Moshe · Oct 13, 2026
The comprehensive guide to SQL window functions. Master OVER, PARTITION BY, ORDER BY, window frames (ROWS vs RANGE), and ranking recipes.
Read postTry it now
Paste your SQL into sqlfmt — format, validate, and catch typos free in your browser.