On 30-Aug-2011 09:10 , Schutte, Michael D wrote:
<<SNIP dec(8) vs DATE>>
But DATE( TIMESTAMP_FORMAT(DIGITS(dcodte), 'MMDDYYYY') ) is
interesting. Can it be used for CYYMMDD 7,0 fields?

The documentation for TIMESTAMP_FORMAT is very limited and even deceptive; there is a very clear implication for instance, that all format-string elements must be separated by one of the supported delimiters, but that is apparently false according to those who have performed actual testing. So be warned that if the function is "corrected" to match [what I infer is meant by] the documentation, then the above example could stop working. Interested parties probably would best submit an e-comment on the documentation to get [or await] clarification before coding to an assumption that the feature is working correctly; hmmmm... for some reason I thought I already had, but I know I have never received any response.

There appears not to be any equivalent for a 'C' element of the format-string in that function. The YY and RR "format-string" elements\specifications available on the function may assist, but the leftmost digit [the 'C'] must be removed, making the expression more complex. And I can only presume the YY as an element of the format-string will match the 1940-2039 100-year window of the OS, but the documentation says nothing; the effects of RR are described in a table. So depending also on the possible values of 'C' for 'CYYMMDD' for the data [e.g. just zero and one], I would expect that the use of either of the following might provide the desired result within a known and consistent 100-year window:

TIMESTAMP_FORMAT(RIGHT(DIGITS(cyymmdd_field),6),'YYMMDD')

TIMESTAMP_FORMAT(RIGHT(DIGITS(cyymmdd_field),6),'RRMMDD')

Otherwise the expression becomes more complicated\verbose.

Somewhat moot however, for the original\subject requirement, as another message clarified that the TIMESTAMP_FORMAT function is [like a UDF] not supported in a derived index. The details from the above response hopefully are still valuable outside of that specific utilization. FWiW I will reply to a prior message of mine, that I think the restriction may be due to a NOT DETERMINISTIC designation for that scalar function, versus anything I had previously alluded.

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.