topic How to convert to date in QlikView

How to convert to date

Wed, 21 Dec 2011 13:04:49 GMT

Hi Everyone

Hoe to convert into a date format, please advice

How to convert to date

swuehl — Wed, 21 Dec 2011 13:25:13 GMT

Try this to read your field in as a timestamp and format it according your standard timestamp format:

INPUT:

LOAD Values,

Timestamp(Date#([Reported Date+],'DD/MM/YYYY hh:mm:ss tt')) as [Reported DateTime+]

FROM

[.\dates.xlsx]

(ooxml, embedded labels, table is Sheet1);

How to convert to date

SunilChauhan — Wed, 21 Dec 2011 13:36:32 GMT

LOAD Values,

timestamp( [Reported Date+]) as date

FROM

(ooxml, embedded labels, table is Sheet1);

How to convert to date

swuehl — Wed, 21 Dec 2011 13:39:25 GMT

Sorry, I haven't seen that your date field has multiple formats!

You could try using alt() function to check multiple formats:

LOAD Values,

Timestamp(alt([Reported Date+], Timestamp#([Reported Date+],'DD/MM/YYYY hh:mm:ss TT') )) as [Reported DateTime+]

FROM

[.\dates.xlsx]

(ooxml, embedded labels, table is Sheet1);

I am not sure what your correct format codes are, especially for the first few values, which already seems to be in a numerical date format, but show a month gap inbetween.

Hope this helps,

Stefan

How to convert to date

vijay_iitkgp — Wed, 21 Dec 2011 13:45:04 GMT

If you want to convert in into only Date format not in Timestamp then you can use

Date(Floor([Reported DateTime+]),'DD-MM-YYYY') AS Date