Converting decimal to Date

onexcent 0
Viewed 73

I have a column with dates formatted as decimals, for example: 20,210,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)
db2-400 db2 casting date sql
4 Answers

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
1
2022-08-30

fiddle

nbk0

You probably get an error because you have some INT / DECIMAL value which can't be converted to a date using this pattern.
The solution is to create some "safe" conversion function "eating" errors like below.

CREATE FUNCTION DATE_SAFE (P_DT INT)
RETURNS DATE
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION 
    BEGIN 
        RETURN CAST (NULL AS DATE);
    END;
  RETURN DATE (TO_DATE (CAST (P_DT AS CHAR (8)), 'YYYYMMDD'));
END

Usage:

SELECT 
  CONTCLMPDTE
--, DATE (TO_DATE (CAST (CONTCLMPDTE AS CHAR (8)), 'YYYYMMDD'))
, DATE_SAFE (CONTCLMPDTE)
FROM (VALUES 0, 20220830) T (CONTCLMPDTE)

The function returns NULL if the corresponding INT can't be converted to a DATE, and no error is thrown as in case, when you uncomment the commented out line with built-in functions only.

Mark Barinstein0

So after a long couple days and almost pulling my hair out, here is what worked for me.

SELECT date(substr(CONTCLMPDTE,1,4)||'-'||substr(CONTCLMPDTE,5,2)||'-'||substr(CONTCLMPDTE,7,2)) FROM JCTDSC WHERE COMPANYNUMBER={Company Number} AND JOBNUMBER={Job Number} LIMIT 1

This formatted from yyyymmdd to mm/dd/yyyy. It also worked for finding the days between current_date and CONTCLMPDTE using this code.

DAYS(CURRENT_DATE) - DAYS({COntract Compl Date Formatted})

Thank you all for your help!

onexcent0

The value just need to be converted into a string with a date format. Then you can use the date() function to convert to date.

create table qtemp/dec_vals (
        col1  decimal(8,0)  );

insert into qtemp/dec_vals
values (20200830),  (20200831),  (20200901),  (20200902),  (20200903),  (20200904),  (20200905),  (20200906);

select date(substr(char(col1), 5, 2) || '/' || substr(char(col1), 7, 2) || '/' || substr(char(col1), 1, 4))  from qtemp/dec_vals;
jwbradley0
Related