Search code examples
sql-serveroracle-databaset-sqlplsqldate-arithmetic

SQL: Is it possible to SUM() fields of INTERVAL type?

I am trying to sum INTERVAL. E.g.

SELECT SUM(TIMESTAMP1 - TIMESTAMP2) FROM DUAL

Is it possible to write a query that would work both on Oracle and SQL Server? If so, how?

Edit: changed DATE to INTERVAL

Solution

  • Ok, after a bit of hell, with the help of the stackoverflowers' answers I've found the solution that fits my needs.

    
SELECT
  SUM(CAST((DATE1 + 0) - (DATE2 + 0) AS FLOAT) AS SUM_TURNAROUND
FROM MY_BEAUTIFUL_TABLE
GROUP BY YOUR_CHOSEN_COLUMN

    This returns a float (which is totally fine for me) that represents days both on Oracle ant SQL Server.

    The reason I added zero to both DATEs is because in my case date columns on Oracle DB are of TIMESTAMP type and on SQL Server are of DATETIME type (which is obviously weird). So adding zero to TIMESTAMP on Oracle works just like casting to date and it does not have any effect on SQL Server DATETIME type.

    Thank you guys! You were really helpful.