Datetime Arithmetic - Oracle to SQL Server Migration

Datetime arithmetic involves addition and subtraction operations on date and time values.

Add and Subtract Days

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 +

Add and Subtract Hours

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

Add Minutes

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

Add Seconds

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

Subtract One Date from Another

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 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

Database and SQL Migration Tools

About SQLines

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

Written by Dmitry Tolpeko - June 2012.

You could leave a comment if you were logged in.