Re: Still more SQL date weirdness

On 01-Jun-2011 09:43 , Dan Kimmel wrote:

The date format within your UDF is WRONG for ISO. ISO string date
format is yyyy-mm-dd. What you have may be acceptable to CQE but it
certainly is not to SQE. You may be experiencing an invocation of CQE
in some of your queries and SQE in others. Change your UDF to include
the hyphens and it should work in all circumstances.

Presumably that is a statement in response to the use of the following literal [or by equivalent expression] form?:
date('00010101000000')

If so, then consider that although DATFMT=*ISO is in effect for the UDF, the DATE() function is not limited to accepting only a date-string in the *ISO form as an argument. The argument could be a positive numeric value, a TIMESTAMP, or a string of various representations, including that of a TIMESTAMP:

The SQL DATE() built-in scalar accepts one expression as argument
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzscadate.htm
That expression, as a string, can be any "valid string representation of a date or timestamp. For the valid formats of string representations of dates and timestamps, see String representations of datetime values:

String representations of datetime values:
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzdtstrng.htm
redirecting more specifically to Timestamps:

Timestamp strings:
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafztsstrings.htm
Suggesting, erroneously, that a valid representation is a "string that starts with a digit and has a length of at least 16 characters"; erroneously because the documentation immediate suggests in the "Table 1" that a valid string representation for a timestamp includes the "14–character form: 'yyyymmddhhmmss'"

Regards, Chuck