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>>
midrange.com
