× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.