Re: SQL extracts of a DSPOBJD *outfile - dates

I did it!!!


CREATE FUNCTION ksqlib.objd_ts (
objcen CHAR(1), objdat CHAR(6), objtim CHAR(6))
RETURNS TIMESTAMP LANGUAGE SQL
DETERMINISTIC CONTAINS SQL
NO EXTERNAL ACTION
BEGIN ATOMIC
DECLARE TSRTN TIMESTAMP ;
DECLARE CEN CHAR(2) ;
IF OBJDAT < '000001' THEN
SET TSRTN = '0001-01-01 00:00:00.000000' ;
RETURN TSRTN ;
ELSE
IF OBJCEN = '0' THEN
SET CEN = '19' ;
ElSE
SET CEN = '20' ;
END IF;
SET TSRTN = to_date(CEN || substr(objdat,5,2) || '-'
|| substr(objdat,1,2) || '-' || substr(objdat,3,2) || ' '
|| substr(objtim,1,2) || ':' || substr(objtim,3,2) || ':'
|| substr(objtim,5,2), 'YYYY-MM-DD HH24:MI:SS') ;
RETURN TSRTN;
END IF;
END



On 12/20/2012 2:04 PM, Gqcy wrote:

Thanks Chuck,
yes, changing my DATFMT would make things much easier.

I am trying to create your "objd_ts" function
in all SQL, but am having trouble...

1) I need a CASE or IF for the century test,..
2) "SET RESULT =" keeps saying that "=" is invalid...



CREATE FUNCTION ksqlib.objd_ts (
objcen CHAR(1), objdat CHAR(6), objtim CHAR(6))
RETURNS TIMESTAMP
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
NO EXTERNAL ACTION
BEGIN
ATOMIC
DECLARE RESULT TIMESTAMP ;
SET RESULT = TIMESTAMP_FORMAT(
'20' || SUBSTR(objdat,5,2) || '-' ||
SUBSTR(objdat,1,2) || '-' ||
SUBSTR(objdat,3,2) || ' ' ||
SUBSTR(objtim,1,2) || ':' ||
SUBSTR(objtim,3,2) || ':' ||
SUBSTR(objtim,5,2), 'YYYY-MM-DD HH24:MI:SS');
RETURN RESULT;
END




On 12/20/2012 12:10 PM, CRPence wrote:

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.