Re: Another SQL newbie question

[CRPence <CRPbottle@xxxxxxxxx>]

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