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.