I have a field defined like that : LAUFD DECIMAL(8,0). In that field, I have for example this value : 20040924 How can I convert this value to a date : 24-09-2004 If I do : select LAUFD, cast(LAUFD as date) from tst_load.MHND I obtain a strange date in 3904 :
sel Substr((Cast(LAUFD As char(8))),7,2)||'-'||Substr((Cast(LAUFD As char(8))),5,2)||'-'|| Substr((Cast(LAUFD As char(8))),1,4)
** you will have the output in the format 'DD-MM-YYYY', but as CHAR and not as DATE data type. If you need the output to be a DATE dat type, then rearrange the substr such that you concatenate in the format 'YYYY-MM-DD' and cast to DATE.