问题:

I need to convert Decimal to date. I have a decimal date field that contains data like this :

1,132,009.00 --1/13/2009
7,152,004.00 --7/15/2004
11,012,005.00 --11/01/2005

etc

I would like it to read as xx/xx/xxxx.

Is there anyway to do this with SQL commands or DB2 logic in a select statement?

SELECT  column1  from  table1  ;

回答1:

WITH x(decvalue) AS ( VALUES (DECIMAL(1132009.00)),(DECIMAL(7152004.00)),(DECIMAL(11012005.00)) )

SELECT CAST( 
    LPAD( RTRIM( CHAR( INTEGER( decvalue/1000000 ))), 2, '0' ) || '/' || 
    LPAD( RTRIM( CHAR( MOD( decvalue/10000, 100 ))), 2, '0' )  || '/' ||
    MOD( decvalue, 10000 ) 
AS CHAR(10)) 
AS chardateresult
FROM x
;

回答2:

Using the same WITH values as @Fred, I came up with:

WITH x(decvalue) AS ( VALUES (DECIMAL(1132009.00)),(DECIMAL(7152004.00)),(DECIMAL(11012005.00)) )

SELECT TO_DATE(CHAR(CAST(decvalue AS DECIMAL(8,0))), 'MMDDYYYY')
FROM x

This assumes that your input values aren't going to be longer than 8 digits (2 for month, 2 for day, 4 for year), otherwise you'll get an overflow error on the cast. It will also fail if there's not at least some value for each of month, day, and year (00002011 would not work, for example, but 01012011 would).