Re: SQL extracts of a DSPOBJD *outfile - dates

On 20 Dec 2012 15:48, Gqcy wrote:

<<SNIP>>
RETURNS TIMESTAMP LANGUAGE SQL
<<SNIP>>
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') ;

The separators are optional [according to user experiences, so I had submitted a reader comment to have the v7r1 doc updated, and those changes are there] so there should be no reason to have such a complex expression [that includes separators]. Instead the following should suffice [untested, for lack of any access]; plus using concat vs || and using spaces after each comma to ensure compatibility across languages:

return
to_date( dec(objcen + 19, 2)
concat substr(objdat, 5, 2)
concat substr(objdat, 1, 4)
concat objtim
, 'YYYYMMDDHH24MISS'
) ;

But since a 14-byte string of non-delimited digits in the form 'yyyymmddhhmmss' is a valid representation [which can be cast directly into the RETURNS data type] of TIMESTAMP, without using the TO_DATE scalar, the following expression should also be acceptable without the additional complexity of having the database SQL process the TIMESTAMP_FORMAT (synonymous: TO_DATE) scalar function:

return
dec(objcen + 19, 2) concat
substr(objdat, 5, 2) concat
substr(objdat, 1, 4) concat
objtim /* 6 more bytes, beyond prior 8 bytes */
;

IBM i 7.1 Information Center -> Database -> Reference -> SQL reference -> Language elements -> Data types -> Datetime values -> String representations of datetime values
_i Timestamp strings i_
"A string representation of a timestamp is a character or a Unicode graphic string that starts with a digit and has a length of at least 14 characters.

The complete string representation of a timestamp has one of the following forms:
Table 1. Formats for String Representations of Timestamps

Format Name Time Format Example
ISO timestamp 'yyyy-mm-dd hh:mm:ss.nnnnnn' '1990-03-02 08:30:00.010000'

IBM® SQL 'yyyy-mm-dd-hh.mm.ss.nnnnnn' '1990-03-02-08.30.00.010000'

14–char form 'yyyymmddhhmmss' '19900302083000'

Trailing blanks can be included. ..."