date format issue

[#1]

hi ,

I am confused with the use of date#() and Date() function.

I am loading an excel file in which date is in format 02/01/1999. when i am using that column in qlikview its showing value in a no like 35467.

I used date#(datefield) but still result is same. When i used date(date#(datefield)) . its showing null.

why is this happening??

[#2 · Accepted solution]

have you tried date(datefield)?? should work for you

[#3]

thanks.... its working.

Could you please explain me the difference between date() and date#()

[nilesh_gangurde]

Heyyy Hiiiii,

Date#() Converts string to Date.

For Example we have Date in the DD-MMM-YYYY Format as a string then you can use the Date#() Function to convert it into Date.

The script will be as Follows:

Date#(FieldName,'DD-MMM-YYYY') as New_Date,

And Date() function will return the simple date with the default format of opearating system.

and you are getting No. like this 35467 etc. These are the Julien dates.

use the Following Script to get the proper date:

Date(FieldName) as  New_Date

and if you want this date into specific formate then mention the formate inside the date function

for example:

if you want Date like this : 07-Aug-2012

then script will be

Date(FieldName,'DD-MMM-YYYY' ) as New_Date

where "FieldName" is the Name of column in which your excel has Dates.

Regards,

Nilesh Gangurde

[#5]

i could not have explained it better

[#6]

hi Nilesh

Thanks for your ans.

[nilesh_gangurde]

You are Always Welcome...

Regards,

Nilesh Gangurde