topic Re: Make date string into date in QlikView

Make date string into date

pauldamen — Tue, 12 Apr 2016 08:18:04 GMT

Hi,

I have an excel file with the following date column: 2/28/2016 4:15:28 pm.

How can I make a regular date from this in Qlikview (2-28-2016), I tried all functions like Date# and num# but it just won't recognize it as a date.

Any suggestions?

Regards, Paul

sunny_talwar — Tue, 12 Apr 2016 08:19:55 GMT

May be this:

Date(Floor(Date#(DateField, 'M/D/YYYY h:mm:ss TT')), 'M-D-YYYY') as DateField

or this if it is already recognized as timestamp

Date(Floor(DateField), 'M-D-YYYY') as DateField

Chanty4u — Tue, 12 Apr 2016 08:21:05 GMT

load *,

Date(Date#(Field,'2/28/2016 4:15:28 pm'),' D-MM-YYYY') as NewDate;

Anonymous — Tue, 12 Apr 2016 08:22:38 GMT

may be something like this?

Date(Date#(DateField,'YouExcelDateFormat'),'NewDateFormat') as NewDate

Chanty4u — Tue, 12 Apr 2016 08:25:22 GMT

load *,

Date(Date#(Field,D/MM/YYYY h:mm:ss TT''),' D-MM-YYYY') as NewDate;

TimeStamp(Timestamp#(Field,D/MM/YYYY h:mm:ss TT''),' D-MM-YYYY') as NewDate;