Date in script not working

[samuel_brierley]

Hi, first of all thank you for any help.

in my data excel file there is a timestamp in the format 'dd/mm/yy hh:mm' ~( the apostraphies are actually there!)

now in my scipt i use the following to extract the date

Date(mid(Time,2,8),'dd/MM/YYYY') as Date,

for some reason this doesnt work at all, it works for some date but not others, i think it gets confused but I dont know why.

to try n identify what is going wrong I load in just one file and clear as day in the source file the timestamp says '28/04/2014 13:01' but for some reason qlikview is showing the 8/05/2014.

just to make life more dificult I am using a Generic Load

any ideas?

[ashfaq_haseeb]

Hi,

Can you post your Excel here?

Regards

ASHFAQ

[Anonymous]

Hard to tell without seeing your excel file.  Try this

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

[tresesco]

Try using Date#() like:

Date(Date#(mid(Time,2,8),'DD/MM/YYYY')) as Date,

[samuel_brierley]

Im actualy not sure what was wrong but its fixed, I never changed anything but loading in one file at a time worked. must of been a computing error, not sure why though wasnt that many records.

thanks for the answers anyway guys, forum as supportive as always

[its_anandrjs]

As you say it is works for you if not then you can try with below script also

Load

Date(Date#(Mid(Time,2,8),'DD/MM/YYYY'),'DD/MM/YYY' ) as Date

From Location;

[Anonymous · Accepted solution]

Good it works now.

Anyway, a piece of advice...  Format pattern is case sensitive, for example MM is for months, mm is for minutes.  DD and dd maybe work the same way, but it better to be consistent and use capital letters for day/month/year, and small for hours/minutes/seconds, as in DD/MM/YYYY hh:mm:ss

Regards,

Michael

[samuel_brierley]

thats a nice bit oif advice thank you