Re: SQL Date function question -- MIDRANGE-L

The use of a "calendar TABLE" for the scenario presented by the OP, as is often the case for similar scenarios involving date selection for non-date data-types, is probably the better option as compared with using an expression to convert the date-like values into date data-type values. I recall having advocated here for use of such, but do not recall any responses, negative or positive. Though perhaps I am mistaken since I could not find any past posts in a quick search of the archives. Typically for convenience in explanation [by scripting the SQL versus trying to explain in words], I would have had the calendar data generated in a recursive CTE just for the query statement joining to that data; I even seem to recall one example limiting the generated date values to those from MIN() to MAX() of the date equivalents for the column of the existing table being queried. In the further distant past I even suggested [to the horror of some] that the date data type might even continue to be avoided or added but maintained as duplicate data [in part(s)] in a database file to enable better and faster means for some types of selection [e.g. by month] where either date arithmetic was not used or was already handled in other routines; doing the conversion on insert and update, e.g. by triggers, is generally better than doing conversions on [most] every read.

With so few acknowledgments on the use of calendar files, I was often left wondering if the need to JOIN to a calendar file somehow was a turnoff for many. As if the various complicated expressions that often ensure poorer performance for use in selection in non-join queries somehow would be generally better than a join to the calendar table combined with the selection against the indexed date and the matching date-like representations in the original file. Once the join is encapsulated in a VIEW and appropriate index(es) created, there should be little concern for the need to have the join logic; i.e. just define the join once in a VIEW, and then use that VIEW to perform selection on the date data-type field. Having properly defined some specific calendars, the definition and most data should be mostly static; and making a change to the calendar data [e.g. tagging a date as a holiday] would generally be considered better than having to change some source code and recompile to effect the same modification to the processing.

I mostly just directly reply to the how-to inquiries for an expression to convert to dates, without any additional suggestion to consider a calendar-file. I at least had not given up entirely on suggesting the use of calendar tables, since at least on another forum I found two recent messages:

Where GGMMAA is Italian equivalent to English DDMMYY, given a column defined as NUMERIC(6) instead of DEC(8), I suggested possibly the use of a calendar file for their similar scenario:
https://groups.google.com/forum/#!msg/it.comp.as400/nZZGedgviXY/Dhicy8N2ZcIJ

A calendar table can assist greatly for other date-related data which is not so easily extracted\searched with a true date data type; e.g. as with a MONTH or a DayOfWeek:
https://groups.google.com/d/msg/it.comp.as400/plVFvwc5X3U/XilQWjvYAYAJ

Regards, Chuck

On 06-Jul-2011 17:28 , Luis Rodriguez wrote:

Thanks for the link. It is not an obvious solution (maybe we are
hardwired to avoid creating extra tables) but, once you see the
simplicity of it, it seems the right way to go.

On Wed, Jul 6, 2011 at 7:27 PM, Jon Paris wrote:

Check out the SQL date reference in today's blog post Brad -
personally I'm never going to bother wrestling with dates in SQL
again. We'll be writing it up fully later but everything you need
in that Redbook.

http://ibmsystemsmag.blogs.com/idevelop/2011/07/jon-and-susan-get-educated.html

On 06-Jul-2011 13:51 , Bradley Stone wrote:

<<SNIP>>
The file contains a date in the format MMDDCCYY. I need to
compare the year entered in a query with the year in the date.
<<SNIP>>