How to take the difference between two dates?

[threedim]

I have two datetimes:
01-Jan-2003 02:47:27
27-Jan-2003 22:50:30

I have heard of a function datediff but i dont think Oracle has it.

Is there any function in Oracle that will give me tthe difference in HH:MM:SS?

 

[sem]

Just subtract. The difference is number of days, so you need no format it. Regards, Dima

 

[Glowworm27]

Oracle Does not have the Equivilant Microsoft DateDiff Function, however is is not hard to write a basic one.

here is one I use, its pretty basic, and won't do anything special for MOnths with 28,29, or 31 days, and doesn't care about leap year... again its just basic

FUNCTION          "DATEDIFF" (
"DATEPART" IN VARCHAR2
, "DATE1" IN DATE
, "DATE2" IN DATE
) RETURN NUMBER IS
BEGIN
	if DATEPART = 'Day' Then 
	
	   RETURN Date1 - Date2;
	
	End if;
	
	if DATEPART = 'Week' Then 
       Return Trunc((date1 - Date2) / 7);	
	End if;
	
	if DATEPART = 'Month' Then 
		RETURN TRUNC((Date1 - DATE2)/30);
	End if;
	
END;

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[SantaMufasa]

George,

Although Oracle does not have a "DateDiff" function as you prescribe, Oracle's default native date-difference calculation uses "days" to calculate difference. Oracle has, however, a MONTHS_BETWEEN function:

select months_between(sysdate,sysdate-85) Month_diff from dual;

MONTH_DIFF
----------
2.77419355

An Oracle programmer would use your "...((date1 - Date2) / 7)..." code to achieve a "WEEKS_BETWEEN" result.

Your user-defined code for results equivilant to Microsoft's DateDiff Function would certainly warm the heart of a SQL Server-to-Oracle transplant, however.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 21:09 (15Feb05) UTC (aka "GMT" and "Zulu"),
@ 14:09 (15Feb05) Mountain Time

Do you use Oracle and live or work in Utah, USA? Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[Glowworm27]

Thanks again Santa!

I saw the Months_Between Function that is help full too.

I am certainly trying to learn this Oracle stuff, I need a good all around reference book, that covers everything.

I have been lucky so far finding answers here, and in some simple tutorials, but even then the material is hard to find. Because some of my problems are so simple, most tutorials skip over the basics ( where I'm having the trouble) and covering the more advanced stuff.

Thanks Again

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[SantaMufasa]

George,

One of my "before-I-die" goals is to produce a syntax/function equivalancy matrix between Oracle, SQL Server/Sybase, MySQL, and DB2. In the meantime, George, please feel free to post your questions here...answering questions (albeit possibly simple) are why we log into Tek-Tips.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 17:24 (16Feb05) UTC (aka "GMT" and "Zulu"),
@ 10:24 (16Feb05) Mountain Time

Do you use Oracle and live or work in Utah, USA? Then click here to join Utah Oracle Users Group on Tek-Tips.