how to change a date with timestamp to a date format like DD/MM/YYYY?

[jim_chan]

Hi there, 

I have a date data like below screenshot. how to change a date with timestamp to a date format like DD/MM/YYYY?

Please help!

Thank you.

Jim.

[Mohan_hitman]

DATE(ordersubmittime,''DD/MM/YYYY'') AS ordersubmittime,

[BrunPierre]

Date(Date#([Order submit time],'DD MMM YYYY hh:mm'),'DD/MM/YYYY')

[Mohan_hitman]

if u use  this exp the time stamp will disappear right?

[BrunPierre]

@Mohan_hitman  Please say that again.

[MarcoWedel]

In order to get real date values, i.e. only one single integer field value per date, you have to trim the fractional time part of your timestamps or to just interpret the date part of your timestamp strings.

Some possible ways to do this:

table1:
LOAD *,
     DayName([Order submitted time]) as [Order submitted date3];
LOAD Timestamp#([Order submitted time],'DD MMM YYYY hh:mm') as [Order submitted time],
     Date(Floor(Timestamp#([Order submitted time],'DD MMM YYYY hh:mm')),'DD/MM/YYYY') as [Order submitted date],
     Date(Date#(Left([Order submitted time],11),'DD MMM YYYY'),'DD/MM/YYYY') as [Order submitted date2]
Inline [
Order submitted time
01 Sep 2022 12:34
01 Sep 2022 23:45
01 Oct 2022 11:11
01 Oct 2022 22:22
01 Nov 2022 16:36
01 Nov 2022 17:36
01 Dec 2022 01:23
01 Dec 2022 02:34
];

[jim_chan · Accepted solution]

Many thanks Brun! this is correct script!

thank you very much!

[jim_chan]

Thanks Macro it helps alot! 

[Mohan_hitman]

if u use  this exp the time stamp will disappear right?