SQL Reloaded: Oracle vs. SQL Server (Date Operations)

Difference between two dates 

 
    Often is needed to calculate the difference in days (or in any other time unit) between two dates, for example in order to determine the cycle/lead time, further analysis being done based on such data. In SQL Server the difference between two dates can be obtained using the DateDiff function which allows specifying the time unit used. Here’s the use for most important time units: 

-- SQL Server Date Difference 
SELECT ProductID, SellStartDate, SellEndDate, GetDate() CurrentDate , 
DATEDIFF(ss, SellStartDate, IsNull(SellEndDate, GetDate())) NumberSeconds , 
DATEDIFF(mi, SellStartDate, IsNull(SellEndDate, GetDate())) NumberMinutes , 
DATEDIFF(hh, SellStartDate, IsNull(SellEndDate, GetDate())) NumberHours , 
DATEDIFF(d, SellStartDate, IsNull(SellEndDate, GetDate())) NumberDays , 
DATEDIFF(wk, SellStartDate, IsNull(SellEndDate, GetDate())) NumberWeeks , 
DATEDIFF(mm, SellStartDate, IsNull(SellEndDate, GetDate())) NumberMonths , 
DATEDIFF(qq, SellStartDate, IsNull(SellEndDate, GetDate())) NumberQuarters , 
DATEDIFF(yyyy, SellStartDate, IsNull(SellEndDate, GetDate())) NumberYears  
FROM Production.Product   

    In Oracle the difference in days could be obtained using the subtraction operator “-”, therefore in order to get the difference in smaller time units the result should be multiplied 24 for hours, 24*60 for minutes, respectively 24*60*60 for seconds, and divided by 7, 30, 3*30 or 365 in order to approximate the difference in weeks, months, quarters, respectively years. 

-- Oracle Date Difference 
SELECT ProductID, SellStartDate, SellEndDate, SYSDATE , 
(NVL(SellEndDate, SYSDATE)- SellStartDate) * 24 * 60 * 60 NumberSeconds , 
(NVL(SellEndDate, SYSDATE)- SellStartDate) * 24 * 60 NumberMinutes , 
(NVL(SellEndDate, SYSDATE)- SellStartDate) * 24 NumberHours , 
NVL(SellEndDate, SYSDATE)- SellStartDate NumberDays , 
(NVL(SellEndDate, SYSDATE)- SellStartDate)/7 NumberWeeks , 
(NVL(SellEndDate, SYSDATE)- SellStartDate)/30 NumberMonths , 
(NVL(SellEndDate, SYSDATE)- SellStartDate)/(30*3) NumberQuarters , 
(NVL(SellEndDate, SYSDATE)- SellStartDate)/365 NumberYears  
FROM SQLServer.PRODUCT     

    Unfortunately the above approach won’t work if you want to use the result with numeric-based functions like Floor, because the difference is an interval and not a numeric value. If you attempt to use directly the Floor function you’ll get the “ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND”. In order to overcome this issue the dates need to be truncated, the lowest time unit for this being the minutes. Here’s the modified query: 

-- Oracle Date Difference using truncated values 
SELECT ProductID, SellStartDate, SellEndDate, SYSDATE , 
(TRUNC(NVL(SellEndDate, SYSDATE), 'MI')- TRUNC(SellStartDate, 'MI')) * 24 * 60 * 60 NumberSeconds , 
(TRUNC(NVL(SellEndDate, SYSDATE), 'MI')- TRUNC(SellStartDate, 'MI')) * 24 * 60 NumberMinutes , 
TRUNC((TRUNC(NVL(SellEndDate, SYSDATE), 'HH')- TRUNC(SellStartDate, 'HH')) * 24) NumberHours , 
TRUNC(NVL(SellEndDate, SYSDATE))- TRUNC(SellStartDate) NumberDays , 
FLOOR((TRUNC(NVL(SellEndDate, SYSDATE), 'IW')- TRUNC(SellStartDate, 'IW'))/7) NumberWeeks , 
FLOOR((TRUNC(NVL(SellEndDate, SYSDATE), 'MM')- TRUNC(SellStartDate, 'MM'))/30) NumberMonths , 
FLOOR((TRUNC(NVL(SellEndDate, SYSDATE), 'Q')- TRUNC(SellStartDate, 'Q'))/(30*3)) NumberQuarters , 
FLOOR((TRUNC(NVL(SellEndDate, SYSDATE), 'YYYY')- TRUNC(SellStartDate, 'YYYY'))/365) NumberYears FROM  SQLServer.PRODUCT   

    Up until weeks the differences between the two queries are minimal, the important differences resulting for months, quarters and years. The logic used in the Oracle query could be encapsulated in a date function like the below one:

CREATE OR REPLACE FUNCTION DateDiff ( datepart varchar2 , 
startdate date , 
enddate date) RETURN 
NUMBER IS datepart1 varchar2 (4); 
BEGIN 
      datepart1:= CASE WHEN datepart = 'SS' THEN 'MI' ELSE datepart END;
RETURN FLOOR((TRUNC(enddate, datepart1)- TRUNC(startdate, datepart1)) 

* CASE datepart

    WHEN 'SS' THEN 24 * 60 * 60 
   WHEN 'MI' THEN 24 * 60 
   WHEN 'HH' THEN 24 
   WHEN 'DD' THEN 1 
   WHEN 'IW' THEN 1/7 
   WHEN 'MM' THEN 1/30 
   WHEN 'Q' THEN 1/(30*3)

   WHEN 'YYYY' THEN 1/365 
   ELSE 1 
END); END;  

  
    Here’s the above Oracle query rewritten using the Oracle DateDiff function: 

-- Oracle Date Difference 
SELECT  ProductID, SellStartDate, SellEndDate, SYSDATE , 
DateDiff('SS', SellStartDate, NVL(SellEndDate, SYSDATE)) NumberSeconds , 
DateDiff('MI', SellStartDate, NVL(SellEndDate, SYSDATE)) NumberMinutes , 
DateDiff('HH', SellStartDate, NVL(SellEndDate, SYSDATE)) NumberHours , 
DateDiff('DD', SellStartDate, NVL(SellEndDate, SYSDATE)) NumberDays , 
DateDiff('IW', SellStartDate, NVL(SellEndDate, SYSDATE)) NumberWeeks , 
DateDiff('MM', SellStartDate, NVL(SellEndDate, SYSDATE)) NumberMonths , 
DateDiff('Q', SellStartDate, NVL(SellEndDate, SYSDATE)) NumberQuarters , 
DateDiff('YYYY', SellStartDate, NVL(SellEndDate, SYSDATE)) NumberYears 
FROM SQLServer.PRODUCT 

Adding Time Units
  

  Oracle is using the add/subtract operators also in order to add/subtract time units from a Date, while SQL Server provides the DateAdd built-in function for the same type of operation. 

-- SQL Server adding time unit 
SELECT GetDate() CurrentDate , 
DateAdd(ss, 1, GetDate()) AddingSecond , 
DateAdd(mi, 1, GetDate()) AddingMinute , 
DateAdd(hh, 1, GetDate()) AddingHour , 
DateAdd(dd, 1, GetDate()) AddingDay , 
DateAdd(wk, 1, GetDate()) AddingWeek , 
DateAdd(mm, 1, GetDate()) AddingMonth , 
DateAdd(q, 1, GetDate()) AddingQuarter , 
DateAdd(yyyy, 1, GetDate()) AddingYear 

-- Oracle adding time unit 
SELECT to_char(SYSDATE, 'dd-mon-yyyy hh24:mi:ss') CurrentDate , 
to_char(SYSDATE + 1/(24*60*60), 'dd-mon-yyyy hh24:mi:ss') AddingSecond , 
to_char(SYSDATE + 1/(24*60), 'dd-mon-yyyy hh24:mi:ss') AddingMinute , 
to_char(SYSDATE + 1/24, 'dd-mon-yyyy hh24:mi:ss') AddingHour , 
to_char(SYSDATE + 1, 'dd-mon-yyyy hh24:mi:ss') AddingDay , 
to_char(SYSDATE + 7, 'dd-mon-yyyy hh24:mi:ss') AddingWeek , 
to_char(add_months(SYSDATE, 1), 'dd-mon-yyyy hh24:mi:ss') AddingMonth , 
to_char(add_months(SYSDATE, 3), 'dd-mon-yyyy hh24:mi:ss') AddingQuarter , 
to_char(add_months(SYSDATE, 12), 'dd-mon-yyyy hh24:mi:ss') AddingYear 
FROM DUAL    

    In order to provide similar functionality with SQL Server, could be created a DateAdd function also in Oracle: 

--Oracle DateAdd implementation 
CREATE OR REPLACE FUNCTION DateAdd ( 
 datepart varchar2 , 
unit number , 
targetdate date) RETURN 
date IS     resultdate  date; 
BEGIN 
     IF datepart IN ('MM', 'Q', 'YYYY') THEN resultdate  := CASE datepart  
           WHEN 'MM' THEN add_months(targetdate, unit)
         WHEN 'Q' THEN add_months(targetdate, unit*3) 

         ELSE add_months(targetdate, unit*12) 
   END; 
  ELSE resultdate  := targetdate + unit * CASE datepart 

     WHEN 'SS' THEN 1/ (24 * 60 * 60) 
     WHEN 'MI' THEN 1/ (24 * 60) 
     WHEN 'HH' THEN 1 / 24 
     WHEN 'DD' THEN 1 
     WHEN 'IW' THEN 7 
      ELSE 1 
END; 
END IF; 
RETURN resultdate; 
END; 

     Here’s the above Oracle query rewritten using the Oracle DateAdd function: 

-- Oracle using DateAdd function 
SELECT to_char(SYSDATE, 'dd-mon-yyyy hh24:mi:ss') CurrentDate , 
to_char(DateAdd('SS', 1, SYSDATE), 'dd-mon-yyyy hh24:mi:ss') AddingSecond , 
to_char(DateAdd('MI', 1, SYSDATE), 'dd-mon-yyyy hh24:mi:ss') AddingMinute , 
to_char(DateAdd('HH', 1, SYSDATE), 'dd-mon-yyyy hh24:mi:ss') AddingHour , 
to_char(DateAdd('DD', 1, SYSDATE), 'dd-mon-yyyy hh24:mi:ss') AddingDay , 
to_char(DateAdd('IW', 1, SYSDATE), 'dd-mon-yyyy hh24:mi:ss') AddingWeek , 
to_char(DateAdd('MM', 1, SYSDATE), 'dd-mon-yyyy hh24:mi:ss') AddingMonth , 
to_char(DateAdd('Q', 1, SYSDATE), 'dd-mon-yyyy hh24:mi:ss') AddingQuarter , 
to_char(DateAdd('YYYY', 1, SYSDATE), 'dd-mon-yyyy hh24:mi:ss') AddingYear 
FROM DUAL      

    Of course, in order to subtract a time unit then a negative value needs to be provided for the second parameter.