Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone
Hoe to convert into a date format, please advice
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);
LOAD Values,
timestamp( [Reported Date+]) as date
FROM
(ooxml, embedded labels, table is Sheet1);
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
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