Re: UDF returning a Boolean

On 2/4/2012 4:34 PM, CRPence wrote:

On 04-Feb-2012 13:10 , Joe Pluta wrote:

On 2/4/2012 3:00 PM, CRPence wrote:

FWiW: Some limited date formatting [ISO, USA, EUR, JIS, LOCAL] is
available from the CHAR and VARCHAR scalars. Also effectively [the
date would need to be cast as TIMESTAMP] even more capabilities in
date formatting should be available using the VARCHAR_FORMAT [aka
TO_CHAR] scalar.

Limited is being kind. They stink. I don't know if you've ever
tried VARCHAR_FORMAT but I've only ever gotten it to work with two
format strings: "YYYY-MM-DD" and "YYYY-MM-DD HH24:MI:SS". Anything
else gets me a QRY2293 error.

I have not used TO_CHAR. I have no access to a system with that scalar.

If the "job" were not limited to the 2-digit year formats, I expect
that the "LOCAL" representation would suffice for most. That and the
other 4-digit standard forms are likely fairly comprehensive globally;
i.e. although limited, I think CHAR() probably is sufficient most of the
time. Admittedly my preference [as seen in my replies: "dd-Mmm-YYYY"]
is not formatting provided by CHAR(), but my formatting choice is a
/language sensitive/ format beyond a simple choice of delimiter for only
digits.

"Sufficient most of the time" is not acceptable in a business environment. It's long been my contention that the SQL folks just don't get business application development. Date formatting is extremely limited. Such is life.

As to any errors, I can only suggest both that defects do not fix
themselves and IBM service and developers do not [generally] peruse the
web looking for anecdotal reports of problems [that are not being
reported]. And even if they did look, they would not necessarily find them.

I didn't say it WAS an error, only that I RECEIVED an error. As in operator or user error. You suggested that TO_CHAR might be an effective date formatting tool. I'm simply explaining that it is not, using the error message as empirical evidence.

FWiW the error message QRY2293 is the Query/400 run-time report
writer equivalent of CPF9999 of the OS and SQL0901 of the SQL; i.e. they
are all generic escape messages. Any such message by itself is of
little value, as each redirects to the prior messages. For example, the
QRY2293 suggest to "determine the cause of the problem, press F10 or use
the Display Job Log (DSPJOBLOG) command to view the messages that were
logged immediately before this message". Without all of the details
available in a LOG(4 0 *SECLVL) spooled joblog for the failing request
along with the specifics of the request that was issued [e.g. the SQL
statement and details like the column definitions], little worthwhile
comment can be made about the origin of those generic "something bad has
transpired" messages. With those details however, a resolution may
become obvious [e.g. messaging documents a restriction that can be
avoided] or a symptom keyword string could be produced with which a
search of existing APARs and PTFs might suggest a fix to be applied or a
circumvention.

I wasn't submitting a PMR to the list, Chuck, just pointing out that TO_CHAR doesn't work. The underlying message is CPF426B with a reason code of 11, which is (paraphrased) "invalid timestamp format specified". Or to paraphrase further, "User error". Nothing I plan to submit a PMR about because it simply reinforces the fact that SQL sucks at date formatting. Anyone who uses SQL with any regularity understands that. MSSQL gets around the limitation with its own CONVERT function. And as I said, I extended Alan's iDate routine to do what I needed.

Joe