Tuesday, May 21, 2013

DateDiff: Oracle vs. SQL Server


Oracle and SQL server are very different on calculating the difference between two dates, both syntactically and semantically. The following table summarizes their differences.
Syntax Semantics
Oracle date2 - date1 Time elapsed from date1 to date2 measured in days.
SQL Server datediff(unit, date1, date2) The number of date/time boundaries crossed from date1 to date2.

The SQL server's semantics is best demonstrated with the following example.

In both cases, the time elapsed is the same, 10 seconds. But the boundary counts are different. Be careful about the semantic difference. Make sure what you calculate is what you want.

Now it begs the question: how to calculate boundaries crossed in Oracle or time elapsed in SQL server.

Boundaries crossed in Oracle

First, use trunc() function to align dates to their boundaries. Then calculate the difference. For example,

Time elapsed in SQL server

Use second boundaries crossed as an approximation to seconds elapsed. Then convert it to other unit. For example,

No comments: