On 12 Apr 2013 08:42, Stone, Joel wrote:
It seems that the consensus is to NOT convert to a date format,
rather leave it as MM/DD/YY.
That doesn't make sense to me as I am DSPOBJD of all *QRYDFN objects
in a lib and then displaying the output by descending order of
Create-date or Last-Used-Date.
The old 1999 stuff will appear at the top of the list - which is not
what is desired.
Not converting\casting to DATE *does* make sense if the only intent
is presentation vs storage. The ordering can be done separately from
the presentation. Doing so is even more sensible, knowing that the
/date-like/ values may not actually be valid dates; e.g. may be blank.
There is no requirement to use the expression from the select-list as
the ORDER BY specification. I gave an example. Rob did as well, even
if only when using the term /comparison/ vs /sort/ to explain.
Here is the code I came up with.
<<SNIP>>
SELECT
WHEN (&SortDate = 'USED ') +
chgvar &SqlStmt (&SqlStmt *bcat 'UsedDate desc')
OTHERWISE +
chgvar &SqlStmt (&SqlStmt *bcat 'CreateDate desc')
ENDSELECT
<<SNIP>>
For example, the following revision to the above CL would not use the
named expression from the select-list of the SQL, would collate without
any casting to date, yet may not be acceptable for ordering blanks:
SELECT
WHEN (&SortDate = 'USED ') +
chgvar &SqlStmt (&SqlStmt *bcat +
'ODUCEN desc, right(ODUDAT, 2) desc, left(ODUDAT, 4) desc')
OTHERWISE
chgvar &SqlStmt (&SqlStmt *bcat +
'ODCCEN desc, right(ODCDAT, 2) desc, left(ODCDAT, 4) desc,
ODCTIM desc')
ENDSELECT
If ordering blanks should reverse from the above, the expression used
to order by the Used date could force a NULL value; e.g. assuming the
century is simultaneously blank with the other corresponding components
of the date value:
chgvar &SqlStmt (&SqlStmt *bcat +
'NULLIF(ODUCEN,'') desc, right(ODUDAT, 2) desc, left(ODUDAT, 4)
desc')
But since all objects should have a creation date, the following
should work [assuming YY works according to the 100-Year window] for the
Creation date in the OTHERWISE. And will likely fail [IMO should fail],
if ever there is a data mapping error per failure to cast; if for
example there is an invalid value for either a MMDDYY or HHMMSS in the data:
OTHERWISE
chgvar &SqlStmt (&SqlStmt *bcat +
'TIMESTAMP_FORMAT(ODCDAT CONCAT ODCTIM, ''MMDDYYHH24MMSS'') DESC')
midrange.com
