Handling "pseudodates" with SQL

Ed and Mary,

We may get "into trouble" by posting stuff that is not MI related.  However, the answer to your question, if I understand correctly, is you must use the DIGITS scalar function to translate the numeric field into a string so you can then SUBSTR it.  The following example is, hopefully, relevant:  

/*------------------------------------------------------------------------*/
/* This query will show all the Invoices created        */
/* after 8 July that do NOT have a valid Order Time  */
/* placed into the Invoice Header record.  It also      */
/* is an example of how to display an 8.0 "date"      */
/* field in mm/dd/yy format (the century is dropped  */
/* for display purposes).                                        */
/*------------------------------------------------------------------------*/
    Select      All
                ENINV#,
  SUBSTR(DIGITS(ENINDT),5,2) || '/' ||
  SUBSTR(DIGITS(ENINDT),7,2) || '/' ||
  SUBSTR(DIGITS(ENINDT),3,2) AS
                InvDate,
                ENORD,
  SUBSTR(DIGITS(ENORDT),5,2) || '/' ||
  SUBSTR(DIGITS(ENORDT),7,2) || '/' ||
  SUBSTR(DIGITS(ENORDT),3,2) AS
                OrdDate,
                ENORTM,
                ENTYSL

    From        IHA

    Where       ENINDT  > 19990708
     and        ENORTM  = 0

/* ---  End of example  --- */

You may contact me "off-list" for clarification if desired.

rich hart
_______________________________________________
This is the MI Programming on the AS400 / iSeries (MI400) mailing list
To post a message email: MI400-Zwy7GipZuJhWk0Htik3J/[email protected]
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/mi400
or email: MI400-request-Zwy7GipZuJhWk0Htik3J/[email protected]
Before posting, please take a moment to review the archives
at http://archive.midrange.com/mi400.