How to find the difference of a day in Oracle

[Suhas Madap]

Hi,
please help me regarding, How to find the difference of a day in Oracle using SQL. Let me more precise on what i want, Suppose i created a table TMP, in that I've two columns named Date_inserted, Date_marked_for_deletion. I insert the values Date_inserted(Timestamp), for Date_marked_for_deletion i'll insert the value like 2007-01-05 12:00:00 AM. Now I want to find out the difference of a day.

[Mark Spritzler]

There are lots of Date functions available in Oracle. Mostly things like TO_CHAR, passing a second parameter that describes how you want to convert your Date to sctring, which can be converted TO_NUMBER. But that is really a Hack, there are Time Div functions, I just can't think of them off the top of my head right now.

Mark

[Carol Enderlin]

I didn't really follow where you are trying to get the difference of a day.

Does this help?

  
select sysdate, sysdate - 1 from dual 
 
SYSDATE                SYSDATE-1
1/5/2007 10:39:10 AM1/4/2007 10:39:10 AM
 

[Suhas Madap]

Hi,
I didn't really follow where you are trying to get the difference of a day.

What I want, if to list all the records with difference of 1 day, it should not include, Hrs,Mins,Secs.

[Carol Enderlin]

I still don't know what you are asking for.

Do you want to find the value of one of your dates minus (or plus?) 1 day (my previous post attempted to provide an example for that one)?

Do you want to find records where the difference between two dates (date inserted and date marked for deletion) is less than (or perhaps greater than...or perhaps equal to ) one day?

Another stab in the dark:

 
select some_column
from your_table t
where 
t.date_one - t.date_two < 1

You can use the trunc() function to remove the time from the date. Google on oracle date functions.