RE: SQL list *OUTFILE of DSPOBJD by date -- MIDRANGE-L

How about Ted Holt's FMTDATE ?

SELECT 'FMTDATE example: ', fmtdate(040813, 'mdy', 'usa/')
FROM SYSIBM/SYSDUMMY1

returns:
....+....1....+....2....+....
Constant value FMTDATE
FMTDATE example: 04/08/2013

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Anderson, Kurt
Sent: Monday, April 08, 2013 3:54 PM
To: Midrange Systems Technical Discussion
Subject: RE: SQL list *OUTFILE of DSPOBJD by date

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.

--
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.