To get the difference between two date columns we can use DATEDIFF function in SQL Server and in Oracle we can just subtract two columns as shown below
SQL Server DATEDIFF ( DATEPART , STARTDATE , ENDDATE )
Example:
Select Datediff(mm,column1,column2)
Above script will output difference in months, so based on what we specify as a first parameter (datepart) to function we will get different results, below are the list of parameters which can be used to get desired format.
“datepart” parameters:
|
year |
yy, yyyy |
|
quarter |
qq, q |
|
month |
mm, m |
|
Day of year |
dy, y |
|
day |
dd, d |
|
week |
wk, ww |
|
weekday |
dw, w |
|
hour |
hh |
|
minute |
mi, n |
|
second |
ss, s |
|
millisecond |
ms |
|
MICROSECOND |
mcs |
|
nanosecond |
ns |
Oracle
In Oracle as shown below we need to write a little complex script to achieve the desired results as shown below
Select trunc(column1-column2) as Day,
trunc(mod((column1-column2)*24,24)) as Hour,
trunc(mod((column1-column2)*24*60,60)) as Minutes,
trunc(mod((column1-column2)*24*60*60)) as Seconds
From Dual;
If we would like to find difference in months we can use MONTHS_BETWEEN(column1, column2) function as shown below
SELECT
MONTHS_BETWEEN(SYSDATE, SYSDATE+30)
from DUAL;
Thank you for reading this article, check out my other SQL Server vs. Oracle posts.