How to convert to date

[#1]

Hi Everyone

Hoe to convert into a date format, please advice

[swuehl]

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);

[SunilChauhan]

LOAD Values,

    timestamp( [Reported Date+]) as date

FROM

(ooxml, embedded labels, table is Sheet1);

[swuehl]

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

[vijay_iitkgp]

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