Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Subtracting a date from sysdate

 
Mahtab Alam
Ranch Hand
Posts: 391
1
Java MySQL Database PHP
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


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 .





 
Martin Vajsar
Sheriff
Pie
Posts: 3747
62
Chrome Netbeans IDE Oracle
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 391
1
Java MySQL Database PHP
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you Martin , I have to practice it.
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic