Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.4k views
in Technique[技术] by (71.8m points)

database - Convert DB2 SQL Decimal to DATE

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  ;
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)
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
;

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...