Re: Object Last Use Date MDY compare issue

On 11-Feb-2015 14:22 -0600, Steinmetz, Paul wrote:

On Wednesday, February 11, 2015 3:16 PM Mark S Waterbury wrote:

<<SNIP>>

You could then do something like this -- (for example with an
OutFile in QTEMP created with the DSPOBJD command), using SQL:

update QTEMP/DSPOBJD
set ODCDAT = <<SNIP>>

to change all of those fields with dates stored as "MMDDYY" to
"YYMMDD" in one pass over the file ...

Then, you can query more easily, sorting those dates is much
easier, etc.

Yes, that helps a lot.
I still need the century concatenated when comparing LUD.

FWiW, if only the values of ODxCEN IN ('0', '1') are of concern, then using the 100-year window of 1940 to 2039 would allow one way for the expression ODxCEN CONCAT MDY_to_YMD(ODxDAT) to be re-represented within the same physical storage as the ODxDAT field.

So if modifying the physical data is deemed valuable\reasonable [instead of just using logical re-representations of the physical data], then to avoid the separate reference to the ODxCEN field along with each reference to the respective ODxDAT field in comparative evaluations, then the following variant to what Mark offered could also have value:

If the values for the date fields are needed solely [even possibly if just primarily] for comparison or collation purposes and not for presentation, then the values can be re-represented as consecutive values within the 100-year window [spanning xxxCEN=0 and xxxCEN=1]. For example, either of the following expressions [as a much more complex expression than the quoted but revised SET clauses, they could be encapsulated in a UDF to make the UPDATE more succinct] could be used to enable the values to be collated and compared relatively vs the original values enabled to be compared only as unequal or equal:

set ODCDAT = /* MMDDYY to WWMMDD; WW is 00-99 in 100yr Window */
case when substr( ODCDAT, 5, 2 ) between 40 and 99
then digits( decimal( substr( ODCDAT, 5, 2) - 40 , 2 ) )
when substr( ODCDAT, 5, 2 ) between 00 and 39
then digits( decimal( substr( ODCDAT, 5, 2) + 60 , 2 ) )
else ' '
end concat substr( ODCDAT, 1, 4 )

set ODCDAT = /* MMDDYY to WWMMDD; WW is 00-99 in 100yr Window */
case ODCCEN
when '0'
then digits( decimal( substr( ODCDAT, 5, 2) - 40 , 2 ) )
when '1'
then digits( decimal( substr( ODCDAT, 5, 2) + 60 , 2 ) )
else ' '
end concat substr( ODCDAT, 1, 4 )

Performing the inverse to re-represent the windowed date-like value into a user-comprehensible date-like string for presentation is possibly less palatable than other solutions, and thus why the above modifications to the data might best be used only when the date-like data is used _solely_ for comparison and\or collation purposes.