Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
tanmayNobel
Contributor II
Contributor II

How to convert Numeric to date in data load editor

Hi I have data loaded as NUM. It contains date. I want to convert it to date format so that I can load it into the datepicker.

Here is my data:-

Screen Shot 2022-06-23 at 12.44.14 PM.png

I have tried these in the data load editor:-

Load

Date((Floor(Timestamp#([stamp], 'YYYY-MM-DD hh:mm:ss') ))) AS [amidate];

Date((Floor(Num#([stamp], 'YYYY-MM-DD hh:mm:ss') ))) AS [amidate];

 

I have set my date and time to this:

SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';

 

It still loads as Num. Can anyone suggest how to load it in Date?

 

Labels (1)
8 Replies
tamilarasu
Champion
Champion

Try this,

Date(Floor([stamp])) AS [amidate];

tanmayNobel
Contributor II
Contributor II
Author

Tried. It still loads as Numeric.

Screen Shot 2022-06-23 at 1.16.57 PM.pngData Load scriptData Load script

tanmayNobel
Contributor II
Contributor II
Author

The field stamp is loaded from the source in this format 2022-05-18 01:00:00. When I see it on qlik it is in this format:- Screen Shot 2022-06-23 at 1.50.25 PM.png

marcus_sommer

Your field isn't numeric else a string and the string-content is neither a date nor a time else a timestamp. This means you need to adjust the timestamp-variable to be able to interpret the content as timestamp which could be then transformed into a date like mentioned above with floor().

- Marcus 

rupalimane1390
Contributor III
Contributor III

I am also having the same problem, it's the weirdest issue I have come across in qlik. I tried using date(datefield,'DD/MM/YYYY') but did not work

hic
Former Employee
Former Employee

Try

Date(
Floor(
Timestamp#([stamp], 'M/D/YYYY h:mm:ss TT')
),
'YYYY-MM-DD hh:mm:ss') AS [amidate];

rupalimane1390
Contributor III
Contributor III

Tried this too , did not work 😞

marcus_sommer

At first load the field with text() to see the real content of it. If it's a valid number - in regard to the default-formating of numbers or dates/timestamps - a formating-function like date() could be directly applied. But if it differs to it or it are strings then an appropriate converting-function like timestamp#() is needed to get a number. Important is that the specified format-pattern fits exactly to the field-content and to the set default-formating (variables usually set at the beginning of the script - but they could be overwritten n times in the script if it's necessary).

Beside this is the statement "did not work" not really helpful ...