Date pulled from excel is neglecting Year

[divya_anand]

Hi,

The source file that I am trying to load has a date field as shown below:

It clearly is displaying the date in a different format. when I pull this field into QlikView, for some reason it is not considering the year. I tried different combinations of Date & Date# but in vein.

How can I pull the date field in DD/MM/YYYY format without making any changes to the excel sheet?

Thank you.

[Anil_Babu_Samineni]

You can do

Date(Date#(Date, 'Your Excel Format'), 'Required Format')

[divya_anand]

Hi Anil,

Thank you for the response.

Ain't working. It is picking a dummy Year.

Correction, I am using a CSV file and not an excel, If that makes a difference.

[Anil_Babu_Samineni]

This?

Date(Date#(Date, 'MM/DD/YYYY'), 'DD/MM/YYYY')

[techvarun]

May be a sample data will help here

[divya_anand]

Hi Varun,

Here it is.

[Anil_Babu_Samineni]

What are you getting when you change environment to below

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff] TT';

[kavita25]

Try in a simple way..as in the excel file..the date format is MM/DD/YYYY.. so you just need to change it to DD/MM/YYYY

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

Regards,

Kavita

[avinashelite]

I tried with your data file , it didn't give any formatting issue ..share the actual will test

[antoniotiman]

In Your file Date format is DD/MM/YYYY

So

LOAD Date

From .......