Add a date interval to a date in SQL Server can be obtained by using DATEADD Function whereas in Oracle we can just add the value as shown below
SQL Server
DATEADD (datepart , number , date )
Example:
Select DATEADD (m,2,getdate())
as todays date is 06/20/2012 the output of the query is "08/20/2012", 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 |
|
dayofyear |
dy, y |
|
day |
dd, d |
|
week |
wk, ww |
|
weekday |
dw, w |
|
hour |
hh |
|
minute |
mi, n |
|
second |
ss, s |
|
millisecond |
ms |
|
micro second |
mcs |
|
nanosecond |
ns |
Oracle
When it comes to oracle we can just add the numbers and 1 represents one day as shown below
SELECT SYSDATE AS current_date,
SYSDATE + 1 AS plus_1_day,
SYSDATE + 1/24 AS plus_1_hours,
SYSDATE + 1/24/60 AS plus_1_minutes,
SYSDATE + 1/24/60/60 AS plus_1_seconds
FROM dual;
If we would like to add a month we can use ADD_MONTHS (column1, value) function as shown below
SELECT SYSDATE,
ADD_MONTHS(SYSDATE, 2)
from DUAL;
Thank you for reading this article, check out my other SQL Server vs. Oracle posts.