This week's book giveaway is in the Jobs Discussion forum.
We're giving away four copies of Java Interview Guide and have Anthony DePalma on-line!
See this thread for details.
The moose likes Oracle/OAS and the fly likes Subtracting a date from sysdate Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Java Interview Guide this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Subtracting a date from sysdate" Watch "Subtracting a date from sysdate" New topic

Subtracting a date from sysdate

Mahtab Alam
Ranch Hand

Joined: Mar 28, 2012
Posts: 388

If today is March 12, 2014, at exactly 12 o'clock noon, what will be the result of executing the statement SELECT sysdate - to_date('12-Mar-2014') FROM dual;
It gives me 0.5

I know sysdate also contains time but how it gives 0.5 .

Oracle Java Programmer , Oracle SQL Expert , Oracle Java Web Component Developer, Oracle Web Service Developer
Martin Vajsar

Joined: Aug 22, 2010
Posts: 3733

Oracle's date arithmetic represents differences of dates as a number of days. The fractional part represents part of a day. In your case, you're subtracting a midnight from a noon, which gives exactly one half of a day, thus 0.5.

the TIMESTAMP data type uses a different arithmetic (subtracting timestamps results in an interval datatype, for example), but I've always found subtracting the dates in the way mentioned above much easier to grasp. Of course, there are no timezones (and subsequently DST) in the DATE type, which makes the calculations much easier, but also imprecise if you need to reflect DST in your computations.
Mahtab Alam
Ranch Hand

Joined: Mar 28, 2012
Posts: 388

Thank you Martin , I have to practice it.
I agree. Here's the link:
subject: Subtracting a date from sysdate
jQuery in Action, 3rd edition