Integer to date conversion - Error

Analytics

Integer to date conversion - Error

Here is the query i am trying to run :

select cast(cast(cast(cal.cal_id as integer format '9999999999') as varchar(10) ) as timestamp(0) format 'YYYYMMDDHH') from miesa01.calen cal

Here cal_id is an integer column.. i have tried all the variations for this .. the strange part is the following query works perfectly fine

select cast(cast(cast(2007090909 as integer format '9999999999') as varchar(10) ) as timestamp(0) format 'YYYYMMDDHH') from miesa01.D_CAL_CALENDAR cal

Please try to pur your ideas and experience.

Thanks,
Laxmi
2 REPLIES
Senior Apprentice

Re: Integer to date conversion - Error

Hi Laxmi,
providing the actual error is more helpful than just "doesn't work".

And why do you cast cal_id to an integer if it's already an integer?

Dieter
Enthusiast

Re: Integer to date conversion - Error

Laxmi,

Maybe the following structure helps you:

create table test (id INTEGER);

INSERT TEST VALUES (2007090909);
INSERT TEST VALUES (2007110914);

SELECT CAST((CAST (ID AS CHAR(10))) AS TIMESTAMP FORMAT 'YYYYMMDDHH')
FROM TEST;

Good luck!