Datediff Seconds Oracle

When I am trying to calculate the date difference by using datediff function it showing that invalid identifier.

Can you please tell me what is the function to calculate date difference.

Can anyone tell me how to subtract 2 timestamps and get the difference in seconds? 'extract' doesn't work, it always gives a value between 0-59, I want the total seconds, not the seconds part of the interval. I'm using Oracle Database 10g Express Edition Release 10.2.0.1.0. How to create a counter in HRS between 'now' and last update. I have a transaction table t with a statusdate column of type date. I would like to create a diff column in hours so have been trying it between systimestamp and the statusdate column cast as timestamp.

Santhosh KancherlaSanthosh Kancherla

6 Answers

There is no DATEDIFF() function in Oracle. On Oracle, it is an arithmetic issue

Mikhail TimofeevMikhail Timofeev

Oracle support Mathematical Subtract - operator on Data datatype. You may directly put in select clause following statement:

Check the EXAMPLE for more visibility.

In case you need the output in termes of hours, then the below might help;

Nadeem_MKNadeem_MK

6,17677 gold badges3535 silver badges5151 bronze badges

You can not use DATEDIFFbut you can use this (if columns are not date type):

you can see the sample

Ersin GülbaharErsin Gülbahar

3,9261313 gold badges4848 silver badges9191 bronze badges

Davy MachadoDavy Machado

basically the to_char(sysdate,'DDD') returns no of days from 1-jan-yyyy to sysdate so that if subtract two dates it will return that,you will get difference between two dates

select to_char(sysdate,'DDD') -to_char(to_date('19-08-1995','dd-mm-yyyy'),'DDD') from dual;

sandeep veeramallasandeep veeramalla

Oracle DateDiff is from a different product, probably mysql (which is now owned by Oracle).

The difference between two dates (in oracle's usual database product) is in days (which can have fractional parts). Factor by 24 to get hours, 24*60 to get minutes, 24*60*60 to get seconds (that's as small as dates go). The math is 100% accurate for dates within a couple of hundred years or so.E.g. to get the date one second before midnight of today, you could say

select trunc(sysdate) - 1/24/60/60 from dual;

That means 'the time right now', truncated to be just the date (i.e. the midnight that occurred this morning). Then it subtracts a number which is the fraction of 1 day that measures one second. That gives you the date from the previous day with the time component of 23:59:59.

MalcolmMalcolm

Not the answer you're looking for? Browse other questions tagged sqloracle11g or ask your own question.