Hi Joel,
I don't know if this is an answer you'd like, but can you have 2 separate SQL statements?
One for the 21st century, and the other for the 20th century?
The split being whatever arbitrary cut-off year you want.
For example:
21st century:
where substr(ODCDAT,5,2) < 90
20th century:
where substr(ODCDAT,5,2) >= 90
I'm not sure how you intend to use the statement, but another option may be to condition the substringed year and concat the 20 or 19 as needed.
-Kurt
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Stone, Joel
Sent: Monday, April 08, 2013 4:39 PM
To: 'Midrange Systems Technical Discussion'
Subject: SQL list *OUTFILE of DSPOBJD by date
I would like to list the *OUTFILE of DSPOBJD by date.
Since DSPOBJD provides the incredibly ugly date format MMDDYY, I would like to convert it to a date with year 99 going to the end of the descending order.
What is a good method to do this?
Do I have to construct the date such as shown below?
But even then the year 99 pops to the top (it should be at the end).
I think I have to use DATE and CHAR and TRIM to build the date.
How do I force it to take MMDDYY and calc 1900 or 2000?
*MDY??
Is there a simpler, better way? (There must be :))
Thanks!
select ODLBNM,ODOBNM,ODOBTP,
substr(ODCDAT,5,2) || '-' || substr(ODCDAT,1,2)
|| '-' || substr(ODCDAT,3,2) as CreateDate,
ODOBTX
from qtemp/dspqry
order by CreateDate desc
______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs Skyscan service.
For more information please visit
http://www.symanteccloud.com ______________________________________________________________________
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
