Date field with number becomes a datetime field after our move to SaaS

[Mimiek]

Hi All,

On our on premise Qlik Sense, in a raw extract, our date fields are numeric i.e. 42020. This could easily be converted to a date field using the Date() function.

I am currently migrating to SaaS and using the Qlik 'Direct Access Data Gateway'. I notice that all date fields are no longer shown as numbers, but as DateTime, with the Time always being 12:00:00 AM. So for example 3/28/2018 12:00:00 AM. This means that I can no longer use the Date() function, but I am forced to change it to

Date(Floor(TimeStamp#(BSDATCRE,'MM/DD/YYYY hh:mm:ss TT')), 'DD/ MM/YYYY')

Is this normal behavior in the SaaS or can I disable this unwanted conversion from Number to DataTime?

[rwunderlich]

My guess is that the display is just that -- a display format assigned to the field.  The underlying field is probably still numeric.

Are you sure you can't apply the Date() function to the field?

-Rob

[Mimiek]

Hi @rwunderlich, 

Putting the Date() arround my field containing the value 3/28/2018 12:00:00 AM results in an empty field. I noticed this when there was no more data in my facts table. The reason was because I do a filter on a date field in the script. Since all my dates were suddenly empty using the Date() function, I started looking for the cause.

Peter

[marcus_sommer]

If date() or num() return NULL it means that the content isn't recognized as number else a string or NULL. I suggest that you look how these fields are loaded in the very first place and if they come there like 42020 and also isnum() on it returns TRUE. If it's ok. then go to next step in the load-chain ... somewhere will be anything unexpected ...