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