Need help for Date issue

[saradhi_it]

HI

i have two different type date format was there in   excel file

like

1/7/2016 and 31-07-2016

how can convert them in qlikview as single date format.. i used date, date# and ALT function .... i want convert this date in a single date format  and should be done in backend only

Regards

Padhu

[sunny_talwar · Accepted solution]

May be like this:

Date(Alt(Num(Date), Num(Date#(Date, 'D/M/YYYY')), Num(Date#(Date, 'DD-MM-YYYY')))) as Date

[MindaugasBacius]

Or else you could do like this:

LOAD *
  ,Date(date#(Replace(Date,'/', '-'), 'DD-MM-YYYY'), 'YYYY-MM-DD') as [Modified date]
INLINE [
    Date
    1/7/2016
    31-07-2016
];

[swuehl]

With your specific excel sample file, it might be enough to use

SET DateFormat = 'DD-MM-YYYY';

LOAD A as DateField

FROM

[https://community.qlik.com/servlet/JiveServlet/download/1121955-245013/Book1.xlsx]

(ooxml, no labels, table is Sheet1);

That's because the first format is loaded in as number and will be interpreted as date, the second format will be interpreted using the date format.

[saimahasan]

I agree with Stefan's reply. It works.