Datetime Arithmetic - Oracle to SQL Server Migration

Datetime arithmetic involves addition and subtraction operations on date and time values using + and - operators.

In Oracle and SQL Server, when you add an integer value to a datetime, you add days:

Oracle:

  ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
 
  -- Output the current date and time
  SELECT SYSDATE FROM dual;
  # 2012-06-11 00:39:06
 
  -- Add 3 days to the current day
  SELECT SYSDATE + 3 FROM dual;
  # 2012-06-14 00:39:06

SQL Server:

  -- Output the current date and time
  SELECT GETDATE();
  # 2012-06-11 00:39:06.587
 
  -- Add 3 days to the current day
  SELECT GETDATE() + 3;
  # 2012-06-14 00:39:06.587

To subtract days, just use - operator instead of +

In Oracle, you can use n/24 expression to add n hours to a datetime:

Oracle:

  -- Output the current date and time
  SELECT SYSDATE FROM dual;
  # 2012-06-11 00:39:06
 
  -- Add 3 hours
  SELECT SYSDATE + 3/24 FROM dual;
  # 2012-06-11 03:39:06

To use n/24 expression to add hours in SQL Server, you have to cast it to FLOAT data type first. Also you can use DATEADD function to add hours in SQL Server.

SQL Server:

  -- Output the current date and time
  SELECT GETDATE();
  # 2012-06-11 00:39:06
 
  -- Add 3 hours
  SELECT GETDATE() + CONVERT(FLOAT, 3)/24;
  # 2012-06-11 03:39:06
 
   -- Add 3 hours
  SELECT DATEADD(hh, 3, GETDATE());
  # 2012-06-11 03:39:06

To subtract hours use - instead of + in Oracle, and specify a negative value in the second operand in SQL Server:

SQL Server:

  -- Subtract 3 hours
  SELECT GETDATE() - CONVERT(FLOAT, 3)/24;
  # 2012-06-10 21:39:06
 
  -- Subtract 3 hours
  SELECT DATEADD(hh, -3, GETDATE());
  # 2012-06-10 21:39:06
 
  -- Using a variable
  DECLARE @sub INT = 3;
  SELECT DATEADD(hh, -@sub, GETDATE());
  GO
   # 2012-06-10 21:39:06

In Oracle, you can use n/1440 or n/(24*60) expression to add n minutes to a datetime:

Oracle:

  -- Output the current date and time
  SELECT SYSDATE FROM dual;
  # 2012-06-11 00:39:06
 
  -- Add 3 minutes
  SELECT SYSDATE + 3/1440 FROM dual;
  # 2012-06-11 00:42:06

In SQL Server, you can use DATEADD function or CONVERT(FLOAT, n)/1440 expression:

SQL Server:

  -- Output the current date and time
  SELECT GETDATE();
  # 2012-06-11 00:39:06
 
   -- Add 3 minutes
  SELECT GETDATE() + CONVERT(FLOAT, 3)/1440;
  # 2012-06-11 00:42:06
 
   -- Add 3 minutes
  SELECT DATEADD(mi, 3, GETDATE());
  # 2012-06-11 00:42:06

In Oracle, you can use n/86400 or n/(24*60*60) expression to add n seconds to a datetime:

Oracle:

  -- Output the current date and time
  SELECT SYSDATE FROM dual;
  # 2012-06-11 00:39:06
 
  -- Add 3 seconds
  SELECT SYSDATE + 3/86400 FROM dual;
  # 2012-06-11 00:39:09

In SQL Server, you can use DATEADD function or CONVERT(FLOAT, n)/86400 expression:

SQL Server:

  -- Output the current date and time
  SELECT GETDATE();
  -- 2012-06-11 00:39:06
 
  -- Add 3 seconds
  SELECT GETDATE() + CONVERT(FLOAT, 3)/86400;
  -- 2012-06-11 00:39:09
 
  -- Add 3 seconds
  SELECT DATEADD(ss, 3, GETDATE());
  -- 2012-06-11 00:39:09

In Oracle if you subtract one date from another you get the number of days between these 2 dates.

Oracle:

  -- Get the number of days between 2 dates
  SELECT TO_DATE('17-FEB-2013', 'DD-MON-YYYY') -  TO_DATE('14-FEB-2013', 'DD-MON-YYYY') FROM dual;
  # 3

In SQL Server, you firstly have to convert datetimes to FLOAT and then apply subtraction operator:

SQL Server:

 -- Get the number of days between 2 dates
  SELECT CONVERT(FLOAT, CONVERT(DATETIME, '17-FEB-2013')) -  
              CONVERT(FLOAT, CONVERT(DATETIME, '14-FEB-2013'));
  # 3

If you subtract two dates in Oracle that have different time parts then the result of the subtraction is a fractional value:

Oracle:

  -- Get the number of days between 2 dates with different time
  SELECT 
     TO_DATE('13-SEP-2024 21:18:04', 'DD-MON-YYYY HH24:MI:SS') -  
     TO_DATE('12-SEP-2024 20:18:04', 'DD-MON-YYYY HH24:MI:SS') 
   FROM dual;
  #  1.04166667

In SQL Server, you firstly have to convert datetimes to FLOAT and then apply subtraction operator:

SQL Server:

  -- Get the number of days between 2 dates with different time
  SELECT 
     CONVERT(FLOAT, CONVERT(DATETIME, '13-SEP-2024 21:18:04')) -  
     CONVERT(FLOAT, CONVERT(DATETIME, '12-SEP-2024 20:18:04')); 
  #  1.04166667

In this example, two dates differ in 1 day and 1 hour, and the fractional part .04166667 is 1/24.

For more information, see Oracle to SQL Server Migration.