topic Re: Remove time from a date field in QlikView

Remove time from a date field

Wed, 25 Nov 2020 16:16:04 GMT

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?

Wed, 28 Jun 2017 19:46:02 GMT

A solution in the load script would be preferred as well

vishsaggi — Wed, 28 Jun 2017 19:53:41 GMT

May be this?

LOAD *,

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

LOAD *,

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

From ....;

Wed, 28 Jun 2017 20:00:13 GMT

I tried that but got this

Wed, 28 Jun 2017 20:01:22 GMT

I have attached test data with excel. Hope this helps

vishsaggi — Wed, 28 Jun 2017 20:04:45 GMT

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

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

vishsaggi — Wed, 28 Jun 2017 20:07:17 GMT

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

Wed, 28 Jun 2017 20:14:45 GMT

Thanks! This helped greatly

vishsaggi — Wed, 28 Jun 2017 20:16:07 GMT

No problem at all.