Load date from oracle number

[linoyel]

Hi, I have a date in Oracle which is stored in an 8 digits number format, e.g. 20200716 and I need to load it into Qlik Sense in a date format: 16/07/2020. The regular formula for this (Load Date(Date#(MyNumericField,'YYYYMMDD'),'DD/MM/YYYY') as Date) doesn't work because it brings it with $numeric and $integer formats, while I need it to be tagged a s a timestamp.

[Taoufiq_Zarra]

Hi,

normally (Load Date(Date#(MyNumericField,'YYYYMMDD'),'DD/MM/YYYY') as Date)  will work

but you can always do that.

Makedate(num(left(MyNumericField,4)),num(right(left(MyNumericField,6),2)),num(right(MyNumericField,2))) as Date 

[linoyel]

Thanks @Taoufiq_Zarra  but what you suggested achieves the same functionality as a regular formula, meaning that the final field looks as date and I can perform date actions on it like Year(Date), but I still need it to be tagged as date / timestamp by qlik sense so I'll be able to use this field in a date picker calendar extension, and it's still tagged as numeric and integer. I think this issue is connected to Oracle data source.

[Taoufiq_Zarra]

@linoyel, Okay, I see the problem.
Unfortunately I don't use that extension.
but once the field is loaded in date format with Date# or makedate in the "LOAD" script it is loaded in the internal table of qlik , so the relation with Oracle is finished.

in my opinion it is necessary to look in the format requested for the date is this DD/MM/YYYY or MM/DD/YYYYY  (Extension)

and to be sure you can also do this just to make sure it's not Oracle:

Tmp:
Load
Numericalfield,
...
SQL select from Oracle;

then
Table:
noconcatenate
load Date#() as ... resident Tmp;

drop table Tmp;

now you have no relation with oracle data source.

[linoyel · Accepted solution]

@Taoufiq_Zarra , I tried the resident load as well but still - no date tag. The solution was to explicitly tag this field as a date: TAG MyDateField with $Date. Thank you for all your help and suggestions!