On 31-Aug-2011 11:28 , Tom wrote:
What happens if these numeric supposed-to-be-date fields have an
invalid date value?
On 8/31/2011 7:18 AM, Joe Pluta wrote:
On 8/31/2011 7:06 AM, Schutte, Michael D wrote:
Instead of using iDate, use Date.
Where
Date(digits(post_date) concat '000000') = current_date - 1 days
Ugh. You'd think in 2011 the SQL standard would have some decent
date formatting, rather than having to remember this (or the '-'
replace trick).
A row where POST_DATE=99999999 as an example of "an invalid date
value" would cause the database to attempt to evaluate the equivalent of
DATE('99999999000000') That string [shown as the apostrophe delimited
literal string] as an operand for the DATE() function is not a valid
TIMESTAMP representation, because there is no valid date value with
either a month=99 or day=99, irrespective of the year [although
year=9999 is valid]. With that...
A data mapping error would be the result; as with any similar example
where the POST_DATE [with the additional zero digits] for the TIMESTAMP
representation that does not represent a valid date. And a mapping
error that occurs on selection means the query terminates when that
error is encountered. Some other existing select\omit logic that could
definitively omit or include the row might allow the data mapping error
to be ignored by the database query engine, but AFaIK, only the CASE
logic supports that capability.
That [data mapping error as] effect is a good reason, beyond just the
ability to use an [existing] index on POST_DATE, why I alluded that
POST_DATE should be the lone 'expression' on the left of the equivalence
predicate. The examples that I gave in other messages are expressions
that can evaluate without the query having processed any rows to define
"yesterday" which is then CAST to the data type matching POST_DATE.
So if POST_DATE is DECIMAL(8, 0), a WHERE clause to compare the
POST_DATE without any need to transform its row data into a valid DATE
value could be for example, one of:
where
POST_DATE = dec(VARCHAR_FORMAT(TIMESTAMP_ISO(current_date-1
day),'YYYYMMDD'), 8)
where
POST_DATE = DEC(replace(CHAR(current_date-1 day,ISO),'-',''), 8)
Both of those example predicates would be able to perform the desired
selection without any data mapping error for DATE conversions
irrespective of the various valid digits in any row of POST_DATE data.
In effect the expression on the right of the equal sign is a literal,
because the expression can be evaluated to a specific value even before
the file.column is even known.
Regards, Chuck
midrange.com
