Re: MIDRANGE-L Digest, Vol 12, Issue 1085

On 20 May 2013 15:41, Gary Thompson wrote:

Maybe this helps:
SELECT CAST
(REPLACE ( CHAR(DATE(CURRENT_DATE)), '-', '' )
AS DECIMAL(8,0)) AS CURYYYYMMDD
FROM SYSIBM/SYSDUMMY1

You should be able to run the statement above from STRSQL,
so this shows how to convert CURRENT_DATE to an 8 digit decimal.

Your output should be something like:

....+....1..
CURYYYYMMDD
20,130,520

That query fails to state an important assumption; i.e. that the DATFMT for the SQL environment must be *ISO. Best to always remove any assumptions when easily effected. In this case, the CHAR function has a second argument for when a DATE [expression] is the first argument, and that second argument would best be the value ISO to avoid encountering a /failed assumption/ in the query. Additionally FWiW, the use of the DATE() scalar is redundant and the lack of a space\blank after the comma in the precisions\scale specification would fail syntactically in a language that uses the comma as a decimal separator. The expression shown in the VALUES should be sufficient to avoid failed assumptions with both the DATFMT and DECFMT\DECPNT settings:

values ( dec( replace( Char( current date , iso ), '-', '' ), 8 ) )