Computing the difference between two dates
You can't add two DATE values. If you think about that logically, it makes sense. What does it mean, for example, to add 15-Nov-1961 to 18-Jun-1961? On the other hand, you can subtract one DATE value from another, resulting in the difference in days between the two dates. For example:
BEGIN DBMS_OUTPUT.PUT_LINE ( TO_DATE('15-Nov-1961','dd-Mon-yyyy') - TO_DATE('18-Jun-1961','dd-Mon-yyyy') );END;The output is:
150From this, you can see that 18-Jun-1961 falls 150 days prior to 15-Nov-1961. Be careful with the results of subtracting two DATE values, because if any time-of-day components are involved, you'll get a fractional result. Look at the same subtraction again; this time we've added some carefully chosen times:
BEGIN DBMS_OUTPUT.PUT_LINE ( TO_DATE('15-Nov-1961 12:01 am','dd-Mon-yyyy hh:mi am') - TO_DATE('18-Jun-1961 11:59 pm','dd-Mon-yyyy hh:mi am') );END;The output is:
149.001388888888888888888888888888888889If you're interested in the number of calendar days between the two dates shown in this example, the result is clearly not correct. It is correct from the standpoint of measuring the number of 24-hour periods between the two DATE values, but most humans want to know that the two dates are 150 days apart, not that one DATE value is 149.00139 24-hour periods distant from the other. You can get correct results by applying the TRUNC function to each of the date values:
BEGIN DBMS_OUTPUT.PUT_LINE ( TRUNC(TO_DATE('15-Nov-1961 12:01 am','dd-Mon-yyyy hh:mi am')) - TRUNC(TO_DATE('18-Jun-1961 11:59 pm','dd-Mon-yyyy hh:mi am')) );END;The output is:
150TRUNC eliminates any time-of-day component from the two DATE values, making the resulting subtraction equivalent to the first example in this section, and the result is the number of calendar days between the two dates. TRUNC is a very useful function when working with DATEs, and is discussed in detail later in Section 10.4.
|
