I have a column with dates formatted as decimals, for example: 20,220,830.

I want to convert this number to date format as 08/30/2021

I have tried to use convert and the database shoots me an error that convert is not a valid function. Cast seems to work but, only returns a null value every time.

This statement will validate:

SELECT CAST(CAST(CONTCLMPDTE AS VARCHAR(8)) AS DATE) 
FROM CMSFIL.JCTDSC AS COMPLDATE 

This statement works but, just outputs null. For background I am querying from a Db2 database.

My ultimate goal is to use this converted date to grab the difference from the current day.

Such as

DAY(CURRENT_DATE) - DAY(COMPLDATE)

CodePudding user response：

Converting it to a date, you cqan do it like this

CREATE TABLE JCTDSC (
    CONTCLMPDTE varchar(10)
    
);

INSERT INTO JCTDSC VALUES ('20,220,830')

SELECT date(to_date(REPLACE(CONTCLMPDTE,',',''),'YYYYMMDD')) FROM JCTDSC AS COMPLDATE 

fiddle

CodePudding user response：

DATE (TO_DATE (CAST (20220830 AS CHAR (8)), 'YYYYMMDD'))