<p>We are now using NHibernate to connect to different database base on where our software is installed.  So I am porting many SQL Procedures to Oracle.</p>

<p>SQL Server has a nice function called DateDiff which takes a date part, startdate and enddate.</p>

<p>Date parts examples are day, week, month, year, etc. . . </p>

<p>What is the Oracle equivalent?</p>

<p>I have not found one do I have to create my own version of it?</p>

<p><strong>(update by Mark Harrison)</strong> there are several nice answers that explain Oracle date arithmetic.  If you need an Oracle datediff() see Einstein's answer.  (I need this to keep spme SQL scripts compatible between Sybase and Oracle.)  Note that this question applies equally to Sybase.</p>

[{'answer_id': 44597, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 1, 'selected': False, 'text': '<p>Tom\'s article is very old.  It only discusses the DATE type.  If you use TIMESTAMP types then date arithmetic is built into PL/SQL.</p>\n\n<p><a href="http://www.akadia.com/services/ora_date_time.html" rel="nofollow noreferrer">http://www.akadia.com/services/ora_date_time.html</a></p>\n\n<pre><code>DECLARE\nts_a timestamp;\nts_b timestamp;\ndiff interval day to second;\nBEGIN\n  ts_a := systimestamp;\n  ts_b := systimestamp-1/24;\n  diff := ts_a - ts_b;\n  dbms_output.put_line(diff);\nEND;\n+00 01:00:00.462000\n</code></pre>\n\n<p>or</p>\n\n<pre><code>DECLARE\nts_b timestamp;\nts_a timestamp;\ndate_part interval day to second;\n\nBEGIN\n  ts_a := systimestamp;\n  date_part := to_dsinterval(\'0 01:23:45.678\');\n  ts_b := ts_a + date_part;\n  dbms_output.put_line(ts_b);\nEND;\n\n04-SEP-08 05.00.38.108000 PM\n</code></pre>\n'}, {'answer_id': 48281, 'author': 'andy47', 'author_id': 2661, 'author_profile': 'https://Stackoverflow.com/users/2661', 'pm_score': 2, 'selected': False, 'text': "<p>JohnLavoie - you don't need that. DATE in Oracle is actually a date and time data type. The only difference between DATE and TIMESTAMP is that DATE resolves down to the second but TIMESTAMP resolves down to the micro second. Therefore the Ask Tom article is perfectly valid for TIMESTAMP columns as well.</p>\n"}, {'answer_id': 312993, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 3, 'selected': False, 'text': "<p>I stole most of this from an old tom article a few years ago, fixed some bugs from the article and cleaned it up.  The demarcation lines for datediff are calculated differently between oracle and MSSQL so you have to be careful with some examples floating around out there that don't properly account for MSSQL/Sybase style boundaries which do not provide fractional results.</p>\n\n<p>With the following you should be able to use MSSQL syntax and get the same results as MSSQL such as SELECT DATEDIFF(dd,getdate(),DATEADD(dd,5,getdate())) FROM DUAL;</p>\n\n<p>I claim only that it works - not that its effecient or the best way to do it.  I'm not an Oracle person :)  And you might want to think twice about using my function macros to workaround needing quotes around dd,mm,hh,mi..etc.</p>\n\n<p><strong>(update by Mark Harrison)</strong> added dy function as alias for dd.</p>\n\n<pre><code>CREATE OR REPLACE FUNCTION GetDate \nRETURN date IS today date;\nBEGIN\nRETURN(sysdate);\nEND;\n/\n\nCREATE OR REPLACE FUNCTION mm RETURN VARCHAR2 IS BEGIN RETURN('mm'); END;\n/\nCREATE OR REPLACE FUNCTION yy RETURN VARCHAR2 IS BEGIN RETURN('yyyy'); END;\n/\nCREATE OR REPLACE FUNCTION dd RETURN VARCHAR2 IS BEGIN RETURN('dd'); END;\n/\nCREATE OR REPLACE FUNCTION dy RETURN VARCHAR2 IS BEGIN RETURN('dd'); END;\n/\nCREATE OR REPLACE FUNCTION hh RETURN VARCHAR2 IS BEGIN RETURN('hh'); END;\n/\nCREATE OR REPLACE FUNCTION mi RETURN VARCHAR2 IS BEGIN RETURN('mi'); END;\n/\nCREATE OR REPLACE FUNCTION ss RETURN VARCHAR2 IS BEGIN RETURN('ss'); END;\n/\n\nCREATE OR REPLACE Function DateAdd(date_type IN varchar2, offset IN integer, date_in IN date )\nRETURN date IS date_returned date;\nBEGIN\ndate_returned := CASE date_type\n    WHEN 'mm'   THEN add_months(date_in,TRUNC(offset))\n    WHEN 'yyyy' THEN add_months(date_in,TRUNC(offset) * 12)\n    WHEN 'dd'   THEN date_in + TRUNC(offset)\n    WHEN 'hh'   THEN date_in + (TRUNC(offset) / 24)\n    WHEN 'mi'   THEN date_in + (TRUNC(offset) /24/60)\n    WHEN 'ss'   THEN date_in + (TRUNC(offset) /24/60/60)\n    END;\nRETURN(date_returned);\nEND;\n/\n\nCREATE OR REPLACE Function DateDiff( return_type IN varchar2, date_1 IN date, date_2 IN date)\nRETURN integer IS number_return integer;\nBEGIN\nnumber_return := CASE return_type\n    WHEN 'mm'   THEN ROUND(MONTHS_BETWEEN(TRUNC(date_2,'MM'),TRUNC(date_1, 'MM')))\n    WHEN 'yyyy' THEN ROUND(MONTHS_BETWEEN(TRUNC(date_2,'YYYY'), TRUNC(date_1, 'YYYY')))/12\n    WHEN 'dd'   THEN ROUND((TRUNC(date_2,'DD') - TRUNC(date_1, 'DD')))\n    WHEN 'hh'   THEN (TRUNC(date_2,'HH') - TRUNC(date_1,'HH')) * 24\n    WHEN 'mi'   THEN (TRUNC(date_2,'MI') - TRUNC(date_1,'MI')) * 24 * 60\n    WHEN 'ss'   THEN (date_2 - date_1) * 24 * 60 * 60\n    END;\nRETURN(number_return);\nEND;\n/\n</code></pre>\n"}, {'answer_id': 26465764, 'author': 'Kamyar Gilak', 'author_id': 1780453, 'author_profile': 'https://Stackoverflow.com/users/1780453', 'pm_score': 0, 'selected': False, 'text': "<p>YOU Could write a function in oracle for this</p>\n\n<pre><code>function        datediff( p_what in varchar2, p_d1 in date, p_d2 in date) return number as  l_result    number; \nBEGIN\n      select (p_d2-p_d1) * \n             decode( upper(p_what), 'SS', 24*60*60, 'MI', 24*60, 'HH', 24, NULL ) \n       into l_result from dual; \n\n      return l_result; \nEND;\n</code></pre>\n\n<p>and use it like :</p>\n\n<pre><code>DATEDIFF('YYYY-MM-DD', SYSTIMESTAMP, SYSTIMESTAMP)\n</code></pre>\n"}]
['https://Stackoverflow.com/questions/43819', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/2469/']