About Me

IT Professional with more than 16 years experience in IT especially in the area of full life-cycle of Web/Desktop Applications Development, Database Development, Software Engineering, Consultancy, Data Management, Data Quality, Data Migrations, Reporting, ERP support, etc.

Saturday, February 13, 2010

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.

No comments: