Date Conversion

[sona_sa]

Hi Can anyone change the date from in DD-MMM-YYYY in QlikView. Please find the attached xls for the test purpose.

Thanks

[Gysbert_Wassenaar]

LOAD date(timestamp#(entry_date,'MMM D YYYY hh:mmTT'),'DD-MM-YYYY') as Date

FROM [DATE.xlsx] (ooxml, embedded labels, table is Sheet1);

See attached qvw.

[sona_sa]

Thanks Gysbert, for reply and solution.

But when I am fetching the data from my xls this is providing from 10th of every month. for 1st to 9th date are missing. Please find the attached xls for full month data with 3 months.

[MK_QSL]

Try below two convert your dates...

DATE(ALT(Date#(entry_date,'MMM  D YYYY hh:mmTT'),Date#(entry_date,'MMM DD YYYY hh:mmTT')),'DD-MMM-YYYY') as entry_date

[Gysbert_Wassenaar]

The double spaces throw the timestamp# function off. Try this:

date(timestamp#(replace(entry_date,'  ',' '),'MMM D YYYY hh:mmTT'),'DD-MM-YYYY') as Date

[vardhancse]

Change the date format here.