Remove time from a date field

[#1]

I have a date field with the format as 01/01/2020, 00:00 AM

I want it to only be the date, with no time. I have looked up many forum posts, but nothing recommended seems to work.

For the above data,

date#(Num#([Closed Date]), 'DD/MM/YYYY')

is not working, it still has the time appended on.

Any ideas?

[#2]

A solution in the load script would be preferred as well

[vishsaggi]

May be this?

LOAD *,

          Date(ClosedDate, 'MM/DD/YYYY') AS ClosedDate1;

LOAD *,

Num(Floor(Subfield(ClosedDate, ',', 1))) AS ClosedDateNum

From ....;

[#4]

I tried that but got this

[#5]

I have attached test data with excel. Hope this helps

[vishsaggi · Accepted solution]

Yes so after you get that ClosedDateNum do a Preceding load again as mentioned above.

Date(ClosedDateNum, 'MM/DD/YYYY')  AS ActualDate;

[vishsaggi]

Try this with your excel sheet.

LOAD *,

     Date(ClosedDateNum, 'MM/DD/YYYY') AS ActualDate;

LOAD [Closed Date],

     Num(Floor(Subfield([Closed Date], ',', 1))) AS ClosedDateNum

FROM

test.xlsx

(ooxml, embedded labels, table is Sheet1);

[#8]

Thanks! This helped greatly

[vishsaggi]

No problem at all.