On 20 Dec 2012 08:58, Gqcy wrote:
do I need a UDF to get the 6 position character dates and
times into a recognizable format?


A UDF is not /required/ to effect that. Although using UDFs can certainly be a nicer way to accomplish that goal than other means; at least for an impromptu query request. However for a specific physical file format [e.g. QLIDOBJD for QADSPOBJ as the model output file or "System supplied outfile for DSPOBJD"], I would probably create a VIEW definition or a PROCEDURE to use instead, thus diminishing the value of a having UDF versus repeated expressions. Of course the source for a VIEW or a RESULT SET from a PROCEDURE can similarly benefit, most obviously for readability, from using a UDF over the use of repeated expressions.

Does "recognizable format" imply merely the presentation versus a desire for actual conversion into DATE data type values? If merely for presentation, then the following expression is probably sufficient; even acceptable for the "Display date" column if the DSPOBJD had run with DATFMT(*MDY) for the job, and even for /blank dates/ which would be presented as the non-delimited string value " / / ":
insert( insert(odldat, 5, 0, '/'), 3, 0, '/')
Or similarly for time values, the expression:
insert( insert(odltim, 5, 0, ':'), 3, 0, ':')

The following query is much /nicer/ for its use of User Defined Functions versus repeated expressions, irrespective of where the SQL SELECT is coded; e.g. in a VIEW, impromptu, or a PROCEDURE:

SELECT odlbnm
, odobnm
, odobtp
, objd_ts( ODCCEN, ODCDAT, ODCTIM ) as odcdattim
, objd_ts( ODSCEN, ODSDAT, ODSTIM ) as odsdattim
, objd_ts( ODRCEN, ODRDAT, ODRTIM ) as odrdattim
, objd_ts( ODLCEN, ODLDAT, ODLTIM ) as odldattim
, objd_dt( ODUCEN, ODUDAT ) as odudat
, objd_dt( ODTCEN, ODTDAT ) as odtdat
, objd_ts( ODACEN, ODADAT, ODATIM ) as odadattim
FROM qtemp/od /* from: dspobjd &l/&o &t *full outfile(qtemp/od) */

Note: many of the stored date values for objects [from the OIR] can, unfortunately, have invalid leap day years due to the [IMO poor] implementation of the QLEAPADJ "Leap year adjustment" feature. For this unlikely possibility [outside of locales using "Era dates"], one could code some specific logic to review for each occurrence of LEFT(mmddyy_value,4)='0229' and then /adjust/ the value to be a valid date.


As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.