Re: SQL Date function question

On 08-Jul-2011 04:47 , rob@xxxxxxxxx wrote:

And the pundits are saying that joining to the additional table may
out perform doing the calculations on the fly. Very interesting!

The performance could depend on the situation; the specific query. If selection is required against expressions calculating date values, then that is where the join may give a big payback. If however other selection can easily pare down the results such that the expressions are calculated only for the few rows returned, then the join can end up being almost completely extra overhead, but then the joined rows should be limited to that smaller number of rows. Considering that the calendar is both naturally limited in rows plus static, and that there is a possibility to implement using index-only given the correct index(es), the join should perform well even when other criteria are not selective enough or when the use of host variable in selection on the DATE data requires that the join cursor is a general implementation versus only joining rows from post-selection indexes.

values days(date('2400-12-31')) returns 876,582. So that's how many
rows you might put into your table. And probably all columns would
have their own index

Most "business applications" will never require such a complete calendar; e.g. most businesses will never have a need to know about the year 1492, 1776, or even 1812. And with the exception of supporting human lifespans, I suspect most business-related calendars will never even need to exceed 100 years; many might suffice including only the present and some several future years spanning less than a dozen years, and sometimes possibly only in a separate calendar for archives having a need to represent any dates in the past. Being data versus code, the calendar window can change with ease; i.e. adding more years into the future as required and even optionally purging past years [when that data is also purged from other application tables, or when OUTER JOIN was used and the code and VIEWs were written to consistently deal with the NULL values].

The other advantage of a calendar table is dealing with a "special value" which could correspond either to the NULL value as the DATE or to a specific valid DATE value which is outside the range of the defined application-specific or business-specific calendar dates. Encapsulating the join and some specific selection to either include or exclude "future" dates might provide alternate views named appropriately to reflect that selection, thus also eliminating the requirement for the person referencing the file having to know [the specific selection criteria for] how those "future" dates are properly handled. For example, if an actual date for a field FFDATE had the value 9999-12-31 being used to represent "not fulfilled" and another value of 2011-12-31 as a specific "fulfillment commit date", the selection FFDATE>CURRENT_DATE to select the rows from the TABLE which represent future commitments would probably not produce the intended results, unless that query were directed against a VIEW which included selection FFDATE<>'2011-12-31'.

Regards, Chuck