topic Date Format in QlikView

Date Format

danielnevitt — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have a date field named ReportDate that is in format DD/MM/YYYY hh:mm:ss.

How can I format this to DD/MM/YYYY on my load statement?

Thanks

Daniel

Chanty4u — Tue, 06 Dec 2016 12:14:01 GMT

try

loiad *,

Date(Date#(Datefield,,'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY') as newdate

resident table;

Chanty4u — Tue, 06 Dec 2016 12:16:14 GMT

check sample:

LOAD * Inline [

date

11/02/1991 01:12:55

];

Result:

load *,

Date(Date#(date,'DD/MM/YYYY hh:mm:ss'),'DD/MM/YYYY') as newdate

Resident a;

sunny_talwar — Tue, 06 Dec 2016 12:28:46 GMT

Good links to look at for issues related to dates:

sunny_talwar — Tue, 06 Dec 2016 12:29:41 GMT

Another thing I would suggest is to use a Floor function to truncate the time portion of the date

Date(Floor(DateField)) as Date

marcus_sommer — Tue, 06 Dec 2016 12:30:27 GMT

To get a real date from a timestamp you need to cut the time-part, maybe in this way:

date(floor(ReportDate), 'DD/MM/YYYY')

and if ReportDate isn't regocnized as timestamp you need to convert it per timestamp#() like:

date(floor(timestamp#(ReportDate, 'DD/MM/YYYY hh:mm:ss')), 'DD/MM/YYYY')

- Marcus