Date Conversion Problem

[#1]

Hi All,  I am facing a problem with the date conversion.

I have this excel file in which i have a date column in which i intend to have dates in 'DD/MM/YYYY' format.

But when i import this file into qlikview it appears in 'MM/DD/YYYY'.

Converting these dates using the :

date(date#(date,'DD/MM/YYYY'),'DD/MM/YYYY') as date lends me null values in the frontend.

I also tried using date(makedate(subfield(date,'/',3),subfield(date,'/',2),subfield(date,'/',1)),'DD/MM/YYYY')

but still i am getting null values.  I am not able to fetch dates correctly to the application. I think its a problem of excel file, but i am not able to identify it correctly and solve it.

Any help would be appreciated. 

Thanks in advance. 

P.S. : Attaching the excel file which contains date column.

[Anonymous]

Just this should suffice :

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

[Anonymous]

Hi,

its format issue,not interpretation issue

try:

date(date,'DD/MM/YYYY') as date

Regards

Neetha

[#4]

Hi...But this isn't working as well.

I am still getting dates in 'MM/DD/YYYY' format.

[#5]

Hi...But this isn't working as well.

I am still getting dates in 'MM/DD/YYYY' format.

I need the dates in 'DD/MM/YYYY' format

[maxgro]

this is what i get loading your excel file

SET DateFormat='MM/DD/YYYY';

LOAD date FROM doubt.xlsx

(ooxml, embedded labels, table is Sheet3);

SET DateFormat='DD/MM/YYYY';

LOAD date FROM doubt.xlsx

(ooxml, embedded labels, table is Sheet3);

[#7]

Exactly....

My date is 1st May 2015(01/05/2015),1st june 2015(01/06/2015) and so on....

The '1' is the date part..not the month....

But its showing otherwise and thats the problem.

[Anonymous]

Hi Shashi,

I used your excel spreadsheet:

script:

LOAD date
FROM

(ooxml, embedded labels, table is Sheet3);

in UI Date:

for me its working fine

Regards

Neetha

[#9]

Hi Neetha,

The output u showed is coming for me too...Th issue i am facing is when i want to display in 'DD/MM/YYYY' format.

The 01 is the date.  05,06,07 etc.. are the months..

When i apply date(date,'DD/MM/YYYY') to it i still get in 'MM/DD/YYYY' format.

Its treating the 01 as month instead of date.

[jonathandienst]

Hi

Shashi Nadiger wrote:

The 01 is the date.  05,06,07 etc.. are the months..    

Not true. In the Excel file you attached, 01 is the month and 05, 06, 07 are the days, so you are getting exactly what is in your source file.

HTH

Jonathan