Introduction
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:
Post a Comment