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')
Table 10. Formats for String Representations of Timestamps
Format name: 14–character form 'yyyymmddhhmmss'
Table 29. Cast Scalar Functions
DATE : Returns a DATE from a value
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2022 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
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.