On Mon 05-May-2011 11:32 , James Lampert wrote:
Anybody know of a good way to get an SQL DATE in a view, from a date
stored YYYYMMDD in a zoned decimal(8,0) in the PF?


The "cast scalar function" DATE allows for a character string [expression] in the form 'YYYYMMDDHHMMSS' [as a TIMESTAMP], to cast that string [no delimiters] into a DATE data type with YYYY-MM-DD-HH.MM.SS as the representative date value. That form enables a "simpler" expression than formatting the data as a date string with delimiters.

Thus a field YYYYMMDD of a numeric data type with precision of eight could be cast using the following expression:
DATE(DIGITS(YYYYMMDD) CONCAT '000000')

If the precision is or may not be eight, then instead:
DATE(DIGITS(DEC(YYYYMMDD,8)) CONCAT '000000')

http://publib.boulder.ibm.com/infocenter/iseries/v5r3/topic/db2/rbafzmstdtstrng.htm#dtstrng
Table 10. Formats for String Representations of Timestamps
Format name: 14–character form 'yyyymmddhhmmss'

http://publib.boulder.ibm.com/infocenter/iseries/v5r3/topic/db2/rbafzmstch2func.htm
Table 29. Cast Scalar Functions
DATE : Returns a DATE from a value

Regards, Chuck

This thread ...

Follow-Ups:

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

This mailing list archive is Copyright 1997-2019 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].