Convert String to Date

[Anonymous]

Hi,

I have the ffg data in an excel sheet:

May  1 2012 12:00AM

May   2 2012 12:00AM

May   3 2012 12:00AM

May   4 2012 12:00AM

May   7 2012 12:00AM

May   8 2012 12:00AM

May   9 2012 12:00AM

May 10 2012 12:00AM

May 11 2012 12:00AM

May 12 2012 12:00AM

When  I Load this data into Qlikview I use the ffg LOAD statement:

LOAD

     Timestamp#(XACT_DAT, 'MMM dd yyyy hh:mmtt') AS XACT_DAT

FROM

[May.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

The problem is that the only values that are converted to a Date are:

May 10 2012 12:00AM      May 11 2012 12:00AM      May 12 2012 12:00AM

All the Days with single digits are not converted to a Date,

Could someone kindly assist me with this problem,

Kind Regards

Razak

[#2]

Hi Razak,

I think if you use

LOAD

Timestamp#(XACT_DAT, 'MMM dd yyyy hh:mmtt') AS XACT_DAT

FROM

[May.csv]

(ooxml, codepage is 1252, embedded labels, delimiter is ',', msq);

This will work.

[Miguel_Angel_Baeyens · Accepted solution]

Hi,

If you mean there are some more blanks between the month name and the day number, you may need to remove these so the format pattern applies to all of them:

Timestamp#(Replace(XACT_DAT, '  ', ' '), 'MMM dd yyyy hh:mmtt') AS XACT_DAT

Hope that helps.

Miguel